76
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:
...
- 1,293
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 ...
- 23.9k
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$
...
- 182k
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;...
- 32.3k
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
...
- 182k
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 "...
- 26.9k
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 ...
- 182k
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 ...
- 1,612
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 $$
...
- 2,184
16
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 ...
- 182k
15
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 -...
- 182k
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 ...
- 182k
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 ...
- 256
14
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
...
- 23.9k
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 ...
- 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 ...
- 182k
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 ...
- 5,283
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 ...
- 182k
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 ...
- 41.3k
9
votes
Accepted
PostgreSQL: Auto update updated_at with custom column
Use the spi module's moddatetime
spi module, moddatetime extension
moddatetime — Functions for Tracking Last Modification Time
moddatetime() is a trigger that stores the current time into a timestamp ...
- 64.7k
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 ...
- 56.5k
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
...
- 1,579
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 ...
- 64.7k
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 (...
- 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 ...
- 182k
7
votes
Accepted
Dealing with out-of-range values on a PARTITION BY RANGE
Is there a way to get the min/max values I could store within the existing partitions?
And you also ask in a comment:
Do you know of a way to get the lower and upper bounds of the range by query?
I ...
- 182k
7
votes
Accepted
Escape % inside FORMAT() function in Postgres
The manual clearly says:
the special sequence %% may be used to output a literal % character
- 26.9k
6
votes
Accepted
Change owner of all schema objects
to avoid mentionned error, try changing the order:
CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
...
- 1,263
6
votes
Trigger function to update column
This isn't how a trigger works. You should fire the TRIGGER before the update, then you just use SET on the NEW record.
SET new.dif_area = abs(100 - (OLD.area_pol / (OLD.area_ofi * 100)));
But even ...
- 64.7k
Only top scored, non community-wiki answers of a minimum length are eligible