Mastering PostgreSQL 12 - Third Edition

Writing stored procedures in PL/pgSQL

Now, let's move on, and learn how to write procedures. In this section, you will learn how to write real stored procedures, which were introduced in PostgreSQL 11. To create a procedure, you have to use CREATE PROCEDURE. The syntax of this command is remarkably similar to CREATE FUNCTION. There are just a few minor differences:

test=# \h CREATE PROCEDURECommand: CREATE PROCEDUREDescription: define a new procedureSyntax: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 ...