8 PL/pgSQL Language

Source chapter 3.6 for PostgreSQL 11.

GASource pages: PDF 55-60Original PDF on GitHub

PDF page 55

This article explains the enhancement of the PL/pgSQL language and new objects using PL/pgSQL.

3.6.1 PROCEDURE object

In PostgreSQL 11, PROCEDURE was added as a new schema object. PROCEDURE is almost the same object as FUNCTION without a return value. PROCEDURE is created with the CREATE PROCEDURE statement. Unlike the CREATE FUNCTION statement, there are no RETURNS clause, ROWS clause, PARALLEL clause, CALLED ON clause, etc.

Example 68 PROCEDURE creation

postgres=> CREATE PROCEDURE proc1(INOUT p1 TEXT) AS
    $$
    BEGIN
        RAISE NOTICE 'Parameter: %', p1 ;
    END ;
    $$
    LANGUAGE plpgsql ;
CREATE PROCEDURE

 Information on the created procedure can be referred to from the pg_proc catalog as FUNCTION.
 To execute PROCEDURE, use the CALL statement instead of the SELECT statement. Parentheses
(()) are necessary regardless of the presence or absence of parameters. INOUT can be specified for the
PROCEDURE parameter.

Example 69 Execute PROCEDURE (1)

postgres=> CALL proc1('test') ;
NOTICE:  Parameter: test
CALL
  p1
------
 test
(1 row)

A parameter name can be specified in the CALL statement.

PDF page 56

Example 70 Execute PROCEDURE (2)

postgres=> CALL proc1(p1=>'test') ;
NOTICE:  Parameter: test
CALL
  p1
------
 test
(1 row)

 To display a definition of created PROCEDURE from the psql command, use the \df command same
as FUNCTION. PROCEDURE shows the Type column as "proc". And the value of type column for
FUNCTION was changed to "func". For PROCEDURE, the "prorettype" column of the pg_proc
catalog becomes 0.

Example 71 List of PROCEDURE

postgres=> \df
                      List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | func1 | text             | text                | func
 public | proc1 |                  | INOUT p1 text       | proc
(2 rows)

 Even when referring to or changing the PROCEDURE definition from the psql command, it can be
executed in the same way as FUNCTION.

PDF page 57

Example 72 Display PROCEDURE definition

postgres=> \sf proc1
CREATE OR REPLACE PROCEDURE public.proc1(INOUT p1 text)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        RAISE NOTICE 'Parameter: %', $1 ;
    END ;
    $procedure$

Transaction control

Within procedures, it is possible to control transactions. In PL/pgSQL procedure, COMMIT statement and ROLLBACK statement can be used. It is now possible to control transactions within cursor loops.

Example 73 Transaction control in PROCEDURE

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i) ;
        IF i % 2 = 0 THEN
            COMMIT ;
        ELSE
            ROLLBACK ;
        END IF ;
    END LOOP ;
END ;
$$ ;

 The syntax for transaction control is also provided in languages other than PL/pgSQL.

PDF page 58

PL/Python plpy.commit() / plpy.rollback() PL/Perl spi_commit() / spi_rollback() PL/Tcl commit / rollback

Change of CREATE / ALTER TRIGGER statement

Since it was possible to distinguish between PROCEDURE object and FUNCTION object, EXECUTE PROCEDURE clause of CREATE TRIGGER statement and ALTER TRIGGER statement can also use EXECUTE FUNCTION clause. Both meanings are the same.

3.6.2 Enhancement of variable definition

The following enhancements have been added to PL/pgSQL.

CONSTANT variables

Constants can now be declared by adding the CONSTANT clause to the variable. If an initial value is not specified, no error occurs and the value of the variable is set as NULL. Syntax

DECLARE variable_name CONSTANT data_type [ := initial_value ] ;

Example 74 CONSTANT clause and changing value error

postgres=> do $$
    DECLARE
       cons1 CONSTANT NUMERIC := 1 ;
    BEGIN
       cons1 := 2 ;
    END ;
    $$ ;
ERROR:  variable "cons1" is declared CONSTANT
LINE 5:   cons1 := 2 ;
^

PDF page 59

NOT NULL variables

It is now possible to specify a NOT NULL constraint on a variable. An initial value is required at the time of declaration. An error occurs if an initial value is not specified. Also, assignment of NULL value will result in an error. Syntax

DECLARE variable_name data_type NOT NULL := initial_value ;

Example 75 NOT NULL clause and initial value

postgres=> do $$
    DECLARE
       nn1 NUMERIC NOT NULL ;
    BEGIN
       RAISE NOTICE 'nn1 = %', nn1 ;
    END ;
    $$ ;
ERROR:  variable "nn1" must have a default value, since it's declared
NOT NULL
LINE 3:   nn1 NUMERIC NOT NULL ;
                        ^

SET TRANSACTION statement

In PostgreSQL 11, It is possible to execute the SET TRANSACTION statement in the PL/pgSQL block.

PDF page 60

Example 76 SET TRANSACTION statement

postgres=> DO LANGUAGE plpgsql $$
  BEGIN
      PERFORM 1 ;
      COMMIT ;
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
      PERFORM 1 ;
      RAISE INFO '%', current_setting('transaction_isolation') ;
      COMMIT ;
  END ;
  $$ ;
INFO:  repeatable read
DO