PostgreSQL has always supported functions, which were often referred to as stored procedures. However, there is a distinction between a stored procedure and a function. As I pointed out previously, up until PostgreSQL 10, we only had functions and no procedures.
The point is that a function is part of a larger structure, that is, a transaction. A procedure can contain more than just one transaction. Therefore, it cannot be called by a larger transaction and is a standalone thing.
Here is the syntax of CREATE PROCEDURE:
test=# \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'
} …
The following procedure shows how two transactions can be executed within the very same procedure:
test=# CREATE PROCEDURE test_proc()
LANGUAGE plpgsql
AS $$
BEGIN
CREATE TABLE a (aid int);
CREATE TABLE b (bid int);
COMMIT;
CREATE TABLE c (cid int);
ROLLBACK;
END;
$$;
CREATE PROCEDURE
Note that the first two statements have been committed, while the second transaction has been aborted. You will see what the effect of this change is later in this example.
To run the procedure, we can use CALL:
test=# CALL test_proc();
CALL
The first two tables were committed – the third table has not been created because of the rollback inside the procedure:
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | a | table | hs
public | b | table | hs
(2 rows)
Procedures are an important step toward a complete and fully featured database system.
