Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for the PostgreSQL database system. Questions about PL/pgSQL should probably be tagged "PostgreSQL" as well.

Filter by
Sorted by
Tagged with
8
votes
1answer
7k views

PostgreSQL: Auto update updated_at with custom column [duplicate]

Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger ...
5
votes
2answers
17k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
6
votes
1answer
4k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
7
votes
2answers
14k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
5
votes
3answers
5k views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
3
votes
1answer
90 views

Is it efficient to use plpgsql functions only to validate identifiers and values?

Will my queries be safe from SQL injections if I use plpgsql functions only to validate identifiers and values, instead of executing queries. Here are the example functions: CREATE OR REPLACE ...
1
vote
1answer
3k views

ERROR: ROWS must be positive

Why the error when I run the below in pgAdmin 4 query editor? I copied the create statement in one database and trying to move to a new server. So I right clicked the function in pgAdmin and ...
1
vote
1answer
895 views

How to return rows from multiple tables selected from information_schema

I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be ...
4
votes
1answer
4k views

Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

I am new to postgres, I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
1
vote
0answers
2k views

How to write a comma-separated list of a single scalar variable in PL/pgSQL?

This is related to: Postgres function assign query results to multiple variables https://stackoverflow.com/q/15949337/5419599 I want to loop over values returned in a single column. In other words: ...
36
votes
6answers
33k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
0
votes
0answers
2k views

How can i return Postgresql temp table in POSTGRESQL

I'mn new in PostgreSQL here is my function which I created. but it does not run successfully because something I'm doing wrong. please, can anyone help me in this? CREATE OR REPLACE FUNCTION ...
0
votes
1answer
41 views

Call dynamically a function using plpgsql

I create a function that returns the value of a field when the id is given : CREATE OR REPLACE FUNCTION get_dcteam(id int) RETURNS text AS $$ #print_strict_params on DECLARE dc_team text; BEGIN ...
1
vote
1answer
73 views

Function taking a string as table and column name

I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table: CREATE OR REPLACE FUNCTION survey_participated(_sn text) ...
0
votes
2answers
64 views

Use variables within a function with INSERT INTO

I want to use a variable within the function so i can grab systemstate from several clients and put them into a local table. It looks like this but now i am stuck with making a part variable ...
3
votes
1answer
3k views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
11
votes
3answers
26k views

How can I return multiple rows of records in PL/pgSQL

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD. that is, I want to append to rec so ...
0
votes
1answer
52 views

using PERFORM to insert a string of SELECT statement into a temp table

I am trying to insert data into a temp_table and then truncating the table after analyzing the result. Here is my code: CREATE OR REPLACE FUNCTION validation() RETURNS text AS $$ DECLARE counter ...
1
vote
1answer
119 views

how to execute sql inside a column in postgres

I am trying to execute SQL statements that exist within a column of a database table. The records of this table has 'tests' in each row. sid name sql 1 test1 SELECT source_system_name, ...
2
votes
1answer
2k views

Make a temporary table visible for a function inside a function (or a subfunction)

I have two functions in PL/pgSQL: The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global ...
5
votes
2answers
680 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
2
votes
1answer
29 views

Function returning table : access individual fields

I have a plpgsql function declared like this : CREATE OR REPLACE FUNCTION dynamiccondition_byid(idcond INTEGER, OtherFilters TEXT) RETURNS TABLE (id INTEGER, weight INTEGER) I can get the individual ...
-1
votes
1answer
698 views

Postgresql cant declare cursor

I am trying to declare select statement to cursor and then declare fields from Cursor to variables and then loop over them (the loop is fine , I have problem with cursor declaration ) having syntax ...
4
votes
1answer
2k views

Pass Parameters to trigger function to execute dynamic SQL

I have this code which was solved by Erwin: Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function Now I want the trigger to call the trigger function insert_data_func with parameters (...
3
votes
1answer
772 views

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date): | COLUMN_NAME | TYPE | |---------------|-------------|...
1
vote
2answers
2k views

UPDATE with LIMIT / OFFSET in PL/pgSQL function

In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function: SELECT id FROM tablea WHERE migrated = false; I want to get 2000 records each ...
2
votes
1answer
734 views

Empty table being returned with plpgsql and EXECUTE USING

Why this function return an empty table (while running the plain query from command line returns different values): CREATE OR REPLACE FUNCTION get_area_values (_tbl regclass, _column text, ...
3
votes
2answers
7k views

How can I update multiple tables at once in Postgres?

I have several tables with the exact same structure and I need to update a value in all tables. In order to do so I tried to build the following script: DO $do$ DECLARE i pg_tables%rowtype; BEGIN ...
1
vote
1answer
1k views

Pass OLD & NEW to a function

I've got 2 triggers. triggerA and triggerB that acts upon changes on tableA and tableB respectively. Inside both those triggers I want to use the same function that uses the OLD & NEW variables. I ...
0
votes
1answer
3k views

Change owner of all schema objects

I want to change the owner of all objects in a schema. To do this I use the following function: CREATE OR REPLACE FUNCTION chown(in_schema VARCHAR, new_owner VARCHAR) RETURNS void AS $$ DECLARE ...
4
votes
2answers
165 views

Why colname as input param of function = not good idea?

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and ...
5
votes
2answers
152 views

Joining a function and a view

I have a function get_sa001 and a view Axis_RefCustomer. When I execute my function get_sa001 on it's own for a certain period let's say 2016 - 2017, the execution time is about ~6 seconds. SELECT d....
2
votes
2answers
1k views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
7
votes
1answer
8k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for example)...
2
votes
1answer
74 views

Function which returns a table for a selected period of time

I have a PostgreSQL function on my hands. The function works but the results are very slow. I'm hopping someone will be able to help me reduce the execution time. I myself was thinking along the ...
4
votes
2answers
10k views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
3
votes
2answers
2k views

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not ...
5
votes
1answer
2k views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
1
vote
1answer
83 views

How to send a parameter to a trigger function from an external resource

I have a trigger that executes before delete to audit changes in tables. The problem I have is that one of the fields I save in the xtable_audit table is the user id (of an user table) that is used in ...
4
votes
1answer
406 views

Modify function to use it in multiple triggers

I have tables that have almost the same columns. The first table has the current values and the other table has the old values and an additional column to store the date data is inserted. The second ...
1
vote
2answers
867 views

Insert values from a record variable into a subclass table

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
3
votes
2answers
19k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
1
vote
3answers
2k views

pl/pgsql: Dynamicly get a column name from a record

Greeting, I want dynamicly get a column name from a record. As showing in the code below that I created a cursor and I used a loop to process each record in that cursor by fetching each row to a ...
1
vote
1answer
919 views

PL/pgsql: use cursor to update next record based on a condition from current record

I have this function in Postgresql 9.6 using pl/pgsql. This function is to update a row from a table based on a condition on previous row. The table has Item id(id), year of ownership (year) , ...
2
votes
1answer
75 views

Why would a Greenplum procedure not recognize a view change?

I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
0
votes
2answers
4k views

Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ DECLARE srowdata record; ...
5
votes
1answer
2k views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
6
votes
1answer
19k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
2
votes
1answer
814 views

Call dblink from a trigger function

I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function. The following statement I can execute from ...
2
votes
1answer
7k views

Postgres function return table with extra column

Newbie to Postgres here.. I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it. ...