Scope and structure

PL/vSQL uses block scope, where a block has the following structure:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
    ...
END [ label ];

Declarations

Variable declarations in the DECLARE block are structured as:

variable_name [ CONSTANT ] data_type [ NOT NULL ] [:= { expression | statement } ];

DECLARE
    x int := 3;
    y int := x;

Aliases

Aliases are alternate names for the same variable. An alias of a variable is not a copy, and changes made to either reference affect the same underlying variable.

new_name ALIAS FOR variable;

Here, the identifier y is now an alias for variable x, and changes to y are reflected in x.

DO $$
DECLARE
    x int := 3;
    y ALIAS FOR x;
BEGIN
    y := 5; -- since y refers to x, x = 5
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;

INFO 2005:  x = 5, y = 5

BEGIN and nested blocks

BEGIN contains statements. A statement is defined as a line or block of PL/vSQL.

Variables declared in inner blocks shadow those declared in outer blocks. To unambiguously specify a variable in a particular block, you can name the block with a label (case-insensitive), and then reference the variable declared in that block with:

label.variable_name

For example, specifying the variable x from inside the inner block implicitly refers to inner_block.x rather than outer_block.x because of shadowing:

<<outer_block>>
DECLARE
    x int;
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 1000; -- implicitly specifies x in inner_block because of shadowing
        OUTER_BLOCK.x := 0; -- specifies x in outer_block; labels are case-insensitive
    END inner_block;
END outer_block;

NULL statement

The NULL statement does nothing. This can be useful as a placeholder statement or a way to show that a code block is intentionally empty. For example:

DO $$
BEGIN
    NULL;
END;
$$

Comments

Comments have the following syntax. You cannot nest comments.

-- single-line comment

/* multi-line
comment
*/

Nested stored procedures

Stored procedures that call other stored procedures, also called nested stored procedures, can be useful for simplifying complex functions and reusing code.

You can enable nested stored procedures by setting the EnableNestedStoredProcedures configuration parameter (disabled by default):

--Enable nested calls
=> ALTER DATABASE DEFAULT SET EnableNestedStoredProcedures = 1;

--Disable nested calls
=> ALTER DATABASE DEFAULT SET EnableNestedStoredProcedures = 0;

In the following example, proc2() calls proc1() to insert values into a table:

CREATE PROCEDURE proc1() AS 
$$ 
    BEGIN PERFORM INSERT INTO t_int VALUES(2023);
    END;
$$;

CREATE PROCEDURE proc2() AS $$
BEGIN
    PERFORM CREATE TABLE IF NOT EXISTS t_int(x int);
    PERFORM CALL proc1();
END;
$$;

You can also use this feature to call meta-functions:

CREATE PROCEDURE RUN_ANALYZE_STATS() AS 
$$
    BEGIN PERFORM SELECT analyze_statistics('');
    END;
$$;

CALL run_analyze_stats();

Depth limits

Stored procedures can only be nested up to a depth of 50. If a stored procedure exceeds the call depth, the entire operation is terminated and rolled back.

The stored procedure recursive_proc() calls itself to insert sequential values into a table, but it has no condition to stop before the depth limit. Calling the procedure causes a rollback and no changes are made to the table:

=> CREATE TABLE numbers (n INT);

=> SELECT * FROM numbers;
 n
---
(0 rows) 

=> CREATE OR REPLACE PROCEDURE recursive_proc(x int) AS
$$
BEGIN
    PERFORM INSERT INTO numbers VALUES(x + 1);
    PERFORM CALL recursive_proc(x + 1);
END;
$$;

=> CALL recursive_proc(0);

ERROR 0: Nested stored procedure call exceeds call depth limit
CONTEXT: PL/vSQL procedure recursive_proc line 4 at static SQL
PL/vSQL procedure recursive_proc line 4 at static SQL
PL/vSQL procedure recursive_proc line 4 at static SQL
...

=> SELECT * FROM numbers;
 n
---
(0 rows)