Hans-Juergen Schoenig: Stored procedures in PostgreSQL: getting started

Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike. Stored procedures basics In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command is defined as follows: 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 Essentially, the syntax is pretty close to CREATE FUNCTION. However, there are of course differences which we will discuss a little later. Running stored procedures Before we dive into the differences between functions and stored procedures it makes sense to take a look at a basic example: 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); In the above example you first create a table and then implement a procedure. What’s noteworthy here is that the procedure is called using the CALL command instead of embedding the function call into a normal SQL – but more on that later. Instead of SQL code, you can also make use of PL/pgSQL code just like you would in a function. The following example shows how this can be done: 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 [...]