Stack Overflow
Stack Overflow

Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.

PL/pgSQL is the default procedural language for PostgreSQL. It can be used to write user defined functions and trigger functions. Since PostgreSQL 9.0, PL/pgSQL is installed by default. It can be used in anonymous code blocks using the DO statement, in functions and in procedures (since Postgres 11).

Reference:

4245 questions
1
vote
2 answers

PostgreSQL: `NEW.` returning `NULL` when `AFTER TRIGGER` is fired

Description of the problem: Hi everyone, i have this table: CREATE TABLE COMPORDINE ( CodProdotto CHAR(5) NOT NULL CHECK(CodProdotto ~* '^[0-9]+$'), CodOrdine CHAR(5) CHECK(CodOrdine ~* '^[0-9]+$'), Prezzo REAL NOT NULL, CHECK(Prezzo >=…
noodles
  • 13
  • 4
1
vote
1 answer

What's wrong with this postgres trigger function?

I am new in postgres, created this trigger function to summarize a larger table to another table, I'm using pgAdmin when I run this in query tools, nothing will happen it don't give me an error or give me any successful message, what's wrong with…
SMA
  • 157
  • 1
  • 1
  • 11
1
vote
1 answer

How to return json key as INT and values (array) as an array in postgresql

I have created a json variable VDN which is like so {"80001": ["6", "9"], "80002": ["2", "6", "10"], "80004": ["4", "10", "8"]} I have created a table in which the keys and values separate in two different columns as Varchar. drop function if…
Chloe
  • 127
  • 1
  • 12
1
vote
1 answer

Select rows where nested json array field includes any of values from a provided array in PostgreSQL?

I'm trying to write an sql query that would find the rows in a table that match any value of the provided json array. To put it more concretely, I have the following db table: CREATE TABLE mytable ( name text, id SERIAL PRIMARY KEY, …
Denis Yakovenko
  • 3,241
  • 6
  • 48
  • 82
1
vote
1 answer

Ambiguous column reference - it could refer to either a PL/pgSQL variable or a table column?

I am getting the following error in PostgreSQL: [42702] ERROR: column reference "topicid" is ambiguous Detail: It could refer to either a PL/pgSQL variable or a table column. Where: PL/pgSQL function topics(integer) line 3 at RETURN QUERY I…
volume one
  • 6,800
  • 13
  • 67
  • 146
1
vote
1 answer

T-SQL stored procedure from SQL server to PostgreSQL

For a bit of context: I've written a stored procedure that uses two tables, products (product_id, product_name, description, price, category_id) and supplies (shipment_id, suppliers_id, product_id (FK to products), supply_date, quantity). The…
BreakyBack
  • 11
  • 1
1
vote
1 answer

Postgresql trigger at new row

I created a trigger CREATE TRIGGER trigger_name AFTER INSERT OR UPDATE ON schema.table FOR EACH STATEMENT EXECUTE PROCEDURE audit_trigger(); That seems to work. Then I have the function: CREATE OR REPLACE FUNCTION audit_trigger() RETURNS…
KerryKilian
  • 95
  • 11
1
vote
1 answer

How to return multiple values from a Postgres function?

I'm using this function in PostgreSQL and it works but now I need to return multiple values, e.g.: _value and _prefix. How can I do? CREATE OR REPLACE FUNCTION generate_sequence(_account_id integer, _sequence text) RETURNS TEXT AS $$ DECLARE …
user14788508
  • 5
  • 5
1
vote
2 answers

Syntax error while using EXECUTE format in Postgresql Function to assign variable

I have been trying to create a function that intends to assign a value to a declared variable, and act accordingly based on that value. I use EXECUTE format() for assigning the value to cnt. CREATE OR REPLACE FUNCTION my_function()…
chris
  • 2,490
  • 4
  • 32
  • 56
1
vote
1 answer

Create Trigger to update a row on same table when insert or update happens using postgresql

I am new to plpgsql and trying the below scenario. It would be helpful if someone could help on this issue. I have a table named emp_table and whenever an record is inserted, I called a trigger to update a column record_status with 'U' and when…
Edy
  • 79
  • 5
1
vote
1 answer

Doing a select in a For loop

I am trying to write a For loop like this: DO $$ DECLARE rec TEXT ; rec1 record ; BEGIN FOR rec IN select my_schema_name from public.table1 loop PERFORM set_config('search_path', rec,false); execute 'select * from table2 where status = 'NEW'…
ptilloo
  • 55
  • 1
  • 8
1
vote
1 answer

Access data of variable in PL/PgSQL

I have a PL/PgSQL function like this: CREATE FUNCTION get_value(firstval integer) RETURNS SETOF mytable AS $func$ DECLARE current mytable; BEGIN SELECT fv FROM mytable WHERE fv = fistval INTO current; IF current IS NULL THEN …
CrisMystik
  • 11
  • 1
1
vote
1 answer

org.postgresql.util.PSQLException: ERROR: column "row_count" does not exist

I tried to execute a Trigger function which checks the value of ROW_COUNT and performs the necessary operation. The trigger function is being called by a trigger. The trigger function definition is: create function MB_MDDeleteDefinitionPGSQL()…
Sugata Kar
  • 367
  • 1
  • 10
1
vote
3 answers

Pg sql use ilike operator to search text in array

I have query like _search_text := 'ind' select * from table where (_search_text ILIKE ANY (addresses)) The st.addresses have value like [india,us,uk,pak,bang] It should return each item rows where any item of column addresses contains the string…
Md. Parvez Alam
  • 4,326
  • 5
  • 48
  • 108
1
vote
0 answers

How to declare a while loop which will work until successful completion of the fetch statement in PL/PGSQL

I have a procedure in PL/PGSQL where I have declared a cursor now when I tried to execute this loop while(parents%FOUND) where parents is a cursor var it's showing error : org.postgresql.util.PSQLException: ERROR: operator does not exist: refcursor…
Sugata Kar
  • 367
  • 1
  • 10
1 2 3
…
99 100