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

learn more… | top users | synonyms

29
votes
3answers
22k views

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
25
votes
2answers
16k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
22
votes
4answers
9k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
19
votes
1answer
3k views

Is there a good way to run a trigger for each record in a postgres table?

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...
14
votes
2answers
3k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
13
votes
5answers
8k 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 ...
13
votes
2answers
4k views

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where ...
11
votes
1answer
3k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
10
votes
2answers
777 views

Postgres query plan of a UDF invocation written in pgpsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
10
votes
1answer
2k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
8
votes
1answer
70 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
7
votes
2answers
1k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
6
votes
3answers
640 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
6
votes
2answers
9k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
5
votes
1answer
10k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
5
votes
1answer
798 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
5
votes
1answer
1k views

Check what event called the trigger on PostgreSQL?

I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE. Oracle triggers can check this in a simple 'IF' statement: IF INSERTING, IF UPDATING or even IF DELETING Is there a ...
5
votes
2answers
3k 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
2answers
3k views

Query across multiple schemas, unknown until execution time

I have multiple schemas that I need to query across. The schemas are not known in advance, for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
4
votes
2answers
504 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
4
votes
2answers
845 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
4
votes
3answers
61 views

Use trigger to synchronize columns with fields in json column on insert or update

I'm a bit of a database/postgres beginner,so bear with me. If I have a table, something like this. CREATE TABLE testy ( id INTEGER REFERENCES other_table, name varchar(128) PRIMARY KEY, ...
4
votes
2answers
2k views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
4
votes
2answers
12k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
4
votes
1answer
2k 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 ...
4
votes
2answers
762 views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful ...
3
votes
2answers
414 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
3
votes
1answer
1k views

How to access the number of rows accumulated by RETURNs in PL/pgSQL

When performing RETURN QUERY ... in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
3
votes
2answers
430 views

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" ...
3
votes
1answer
988 views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
3
votes
1answer
579 views

How to turn off all RAISE NOTICE on production server?

I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
3
votes
2answers
406 views

Plpgsql seems to be deleting and inserting instead of updating - Why?

I am using postgreSQL 7.4. I have a large table , call it table_a: key1 INT NOT NULL, key2 INT NOT NULL, data INT NOT NULL, itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, ...
3
votes
2answers
79 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
3
votes
1answer
107 views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
3
votes
1answer
1k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
3
votes
2answers
380 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
3
votes
1answer
967 views

What PL/pgSQL debugger can I use?

I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough. I read about a pgAdmin plugin you can install but it was only on Windows and I'm ...
3
votes
1answer
299 views

PostgreSQL Return Joined Results of Select

I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one: CREATE OR REPLACE FUNCTION api.book_list() RETURNS TABLE ( id BIGINT, published ...
3
votes
1answer
74 views

INSERT inside PL/pgSQL function gives different result

In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function ...
3
votes
1answer
1k views

Distinguish between schema and database

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code: DECLARE v_tmp_host scan.location.alias%TYPE; v_ip_address character varying; BEGIN ...
3
votes
0answers
195 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 ...
2
votes
2answers
4k 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 $$ ... $$ ...
2
votes
2answers
3k views

Recursive query using plpgsql

I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure. I have a data table and a table to link the data together: DATATABLE ----------- id ...
2
votes
2answers
813 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
2
votes
3answers
111 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
2
votes
1answer
6k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
2
votes
2answers
71 views

How to cast to int array in Postgresql?

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc ...
2
votes
2answers
585 views

How to do WHERE x IN (val1, val2,…) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
2
votes
1answer
872 views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...