Procedures vs. functions in PostgreSQL
PostgreSQL's CREATE PROCEDURE syntax closely mirrors CREATE FUNCTION, but the two constructs behave differently in one important respect: transaction control. A procedure can commit or roll back transactions inside its body; a function cannot.
The basic command structure is:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
blog=# h CREATE PROCEDURE Command: CREATE PROCEDURE Description: define a new procedure Syntax: CREATE [ OR REPLACE ] PROCEDURE name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) { LANGUAGE lang_name | TRANSFORM { FOR TYPE type_name } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' } ... URL: https://www.postgresql.org/docs/15/sql-createprocedure.html |
A simple procedure that inserts data shows the main difference at invocation time. Procedures are run with CALL, not embedded in a SELECT statement:
PgSQL
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS t_demo (id int); CREATE OR REPLACE PROCEDURE sample_1(x int) LANGUAGE SQL AS $ INSERT INTO t_demo VALUES (x); $; CALL sample_1(1000); |
You can write procedure bodies in PL/pgSQL just as you would for a function. The key distinction is visible here — a COMMIT appears in the middle of the code:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR REPLACE PROCEDURE sample_2() LANGUAGE plpgsql AS $ DECLARE v_sum int8; BEGIN INSERT INTO t_demo VALUES (1); INSERT INTO t_demo VALUES (2); COMMIT; INSERT INTO t_demo VALUES (3); INSERT INTO t_demo VALUES (4); SELECT sum(id) FROM t_demo WHERE id < 5 INTO v_sum; RAISE NOTICE 'debug info: %', v_sum; END; $; |
Transaction boundaries
In a normal server-side function, all code runs inside the parent transaction. The function is part of the calling statement, so it cannot control when that transaction ends. You cannot, for example, commit after processing the first million rows and then continue:
PgSQL
|
1 |
SELECT func(x) FROM tab; |
A procedure is never called inside a SELECT; it is invoked with CALL, which allows it to manage its own transactions:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
demo=# CALL sample_2(); NOTICE: debug info: 10 CALL demo=# SELECT * FROM t_demo; id ------ 1000 1 2 3 4 (5 rows) |
That means rows written before a COMMIT are in a different transaction than rows written after it. The hidden xmin column, which records the transaction ID that wrote each row, confirms this:
PgSQL
|
1 2 3 4 5 6 7 8 9 |
demo=# SELECT xmin, * FROM t_demo; xmin | id --------+------ 3060775 | 1000 3060780 | 1 3060780 | 2 3060781 | 3 3060781 | 4 (5 rows) |
After a COMMIT inside a procedure, a new transaction starts automatically — there is no explicit BEGIN or START TRANSACTION required. The behavior is analogous to COMMIT AND CHAIN.
Because procedures can span multiple transactions, some restrictions apply. You cannot run a procedure that commits internally inside an explicit transaction block:
PgSQL
|
1 2 3 4 5 6 7 8 9 |
demo=# BEGIN; BEGIN demo=*# CALL sample_2(); ERROR: invalid transaction termination CONTEXT: PL/pgSQL function sample_2() line 7 at COMMIT demo=!# ROLLBACK; ROLLBACK |
If the procedure does not manage transactions itself, however, you can call it from within a transaction block:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE PROCEDURE sample_3() LANGUAGE plpgsql AS $ DECLARE v_sum int8; BEGIN INSERT INTO t_demo VALUES (1); INSERT INTO t_demo VALUES (2); INSERT INTO t_demo VALUES (3); INSERT INTO t_demo VALUES (4); END; $; |
That allows the following to work:
PgSQL
|
1 2 3 4 5 6 7 8 |
demo=# BEGIN; BEGIN demo=*# CALL sample_3(); CALL demo=*# COMMIT; COMMIT |
Exception blocks also impose limits. A transaction cannot be ended inside a block that has exception handlers. In a function, an exception block is an internal subtransaction, which is why this restriction makes sense.
Altering procedures
To change a procedure's settings, use ALTER PROCEDURE. The syntax is:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
blog=# h ALTER PROCEDURE Command: ALTER PROCEDURE Description: change the definition of a procedure Syntax: ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] action [ ... ] [ RESTRICT ] ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] RENAME TO new_name ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] SET SCHEMA new_schema ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] DEPENDS ON EXTENSION extension_name where action is one of: [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET configuration_parameter { TO | = } { value | DEFAULT } SET configuration_parameter FROM CURRENT RESET configuration_parameter RESET ALL URL: https://www.postgresql.org/docs/15/sql-alterprocedure.html |
For example, you can force a procedure to run with UTC as its timezone:
PgSQL
|
1 2 3 |
demo=# ALTER PROCEDURE sample_2() SET timezone TO 'UTC'; ALTER PROCEDURE |
When server-side logic makes sense
Some architects dismiss stored procedures entirely, but there are cases where moving logic to the server is clearly the right call. If you are processing billions of rows, shipping the whole dataset to a client, transforming it there, and sending it back is wasteful. Procedures are a good fit when:
- A large volume of data is processed, so keeping calculations close to the data reduces network overhead
- Transactional integrity is a requirement
- The calculation is inherently data-centric
For more on related topics, see Laurenz Albe's post on subtransactions and performance in PostgreSQL.
