Skip to main content
73 votes

What are the differences between "Stored Procedures" and "Stored Functions"?

PostgreSQL 11 added stored procedures as a new schema object. You can create a new procedure by using the CREATE PROCEDURE statement. Stored procedures differ from functions in the following ways: ...
d4nyll's user avatar
  • 1,263
32 votes
Accepted

Try catch equivalent in Postgres

The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way: [ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR ...
McNets's user avatar
  • 23.8k
22 votes
Accepted

Loop through key/value pairs of a jsonb object in postgresql function

Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like: CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ ...
Erwin Brandstetter's user avatar
19 votes
Accepted

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

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in: CREATE FUNCTION test() RETURNS SETOF RECORD AS $$ DECLARE rec record;...
Daniel Vérité's user avatar
18 votes
Accepted

How to use function parameters in dynamic SQL with EXECUTE?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text ...
Erwin Brandstetter's user avatar
18 votes
Accepted

What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "...
mustaccio's user avatar
  • 26.4k
17 votes
Accepted

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. It's no insurance against ...
Erwin Brandstetter's user avatar
17 votes
Accepted

How do I reraise an exception in a PL/pgSQL EXCEPTION block?

You can use RAISE without any parameters. This is documented on the Errors and Messages page: The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's ...
jpmc26's user avatar
  • 1,602
16 votes

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

Use setof record and return next rec if you want to return multiple records from a function, example: create or replace function test_function() returns setof record language plpgsql as $$ ...
klin's user avatar
  • 2,184
15 votes

Concurrent transactions result in race condition with unique constraint on insert

The root of the problem is that, with default READ COMMITTED isolation level, each concurrent UPSERT (or any query, for that matter) can only see rows that were visible at the start of the query. The ...
Erwin Brandstetter's user avatar
15 votes
Accepted

How to use variables in procedure (not function)?

SQL functions or procedures (LANGUAGE sql) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures ...
Erwin Brandstetter's user avatar
14 votes
Accepted

PostgreSQL use NEW in query for INSTEAD OF trigger

NEW is a record, not a table. Basics: Use NEW in FROM clause in Postgres trigger? Slightly modified setup CREATE TABLE product ( product_id serial PRIMARY KEY , product_name text UNIQUE NOT NULL -...
Erwin Brandstetter's user avatar
14 votes
Accepted

How can I use an environment variable in a Postgres function?

You can not access a environment variable directly; but you can access a custom configuration setting and set that to a value from the environment at startup. For example, you can pass the follow ...
Corin's user avatar
  • 256
13 votes

PostgreSQL Upsert not working on Partitioned Table

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11. In Postgres 9.6: INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON ...
klin's user avatar
  • 2,184
13 votes
Accepted

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

There are various subtle differences. Simple SQL functions can be inlined when nested in an outer query - provided they meet some preconditions. Not possible for a PL/pgSQL function. All statements in ...
Erwin Brandstetter's user avatar
12 votes
Accepted

POSTGRES declaring variable containing date array in functions

You can use ARRAY keyword: create table tbl (dateval date[]); insert into tbl values(array ['20180101','20180102'::date]); Or in a block of code: do $$ declare dateval date[]; begin ...
McNets's user avatar
  • 23.8k
11 votes
Accepted

Concurrent transactions result in race condition with unique constraint on insert

Try the insert first, with on conflict ... do nothing and returning id. If the value already exists, you will get no result from this statement, so you have then to execute a select to get the ID. If ...
CL.'s user avatar
  • 5,243
10 votes
Accepted

RETURN value directly from INSERT with RETURNING clause

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically: CREATE OR REPLACE FUNCTION make_date_from_configuration( ...
Erwin Brandstetter's user avatar
10 votes

How to pass variable to PL/pgSQL code from the command line?

The manual for psql: Variable interpolation will not be performed within quoted SQL literals and identifiers. The body of a DO statement (or function) is a quoted literal - dollar-quoted in this ...
Erwin Brandstetter's user avatar
10 votes
Accepted

When (or why even) use PLPython(3)u

I find pl/pgsql tedious for general programming, and slow to program in and slow to execute. And missing a lot of functionality--some of which is missing because it has to be in order to be a trusted ...
jjanes's user avatar
  • 40.4k
9 votes
Accepted

Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

No, You can't control a transaction inside a plpgsql function (or anonymous block). The only option that you have its creating a transaction outside the block, eg: BEGIN; DO $$ -- function stuff ...
Sebastian Webber's user avatar
9 votes
Accepted

PostgreSQL: Auto update updated_at with custom column

Use the spi module's moddatetime spi module, moddatetime moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field....
Evan Carroll's user avatar
  • 63.9k
9 votes
Accepted

Unable to Create COMMIT inside PostgreSQL 11.5 Procedure

The BEGIN; is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction. You can end a transaction, which implies that a new ...
Laurenz Albe's user avatar
8 votes
Accepted

PL/pgSQL regclass quoting of table named like keyword

Explanation I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution. 0. The reason why overlay is quoted is not because it's a function ...
Erwin Brandstetter's user avatar
8 votes

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

Using row_to_json function: do $$ declare r json; i int; begin for r in select row_to_json(t.*) from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13) loop ...
Abelisto's user avatar
  • 1,569
8 votes
Accepted

How can I update multiple tables at once in Postgres?

You're actually close.. first create some test data.. CREATE TABLE foo_knex_migrations ( name ) AS VALUES ('test.js'),('test2.js'),('bicycles'); CREATE TABLE bar_knex_migrations AS TABLE ...
Evan Carroll's user avatar
  • 63.9k
8 votes
Accepted

PostgreSQL Upsert not working on Partitioned Table

PostgreSQL 11 supports INSERT INTO ... ON CONFLICT with partitioned tables: CREATE TABLE o(id INT PRIMARY KEY, i INT) PARTITION BY RANGE (id); CREATE TABLE o1 PARTITION OF o FOR VALUES FROM (1) TO (...
lad2025's user avatar
  • 364
8 votes
Accepted

ERROR: "sql " is not a known variable

Your error message says: psql:cobertura.sql:29: ERROR: "sql " is not a known variable LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... Look closely: "sql ", not "sql" That ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Assignment of a column with dynamic column name

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE. And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible