PostgreSQL PL/PGSQL Block Structures

PL/pgSQL block structure example:

The following example illustrates a very simple block. It is called an anonymous block.

Input:

DO $$
<>
DECLARE
a integer := 0;
BEGIN
a := a + 1;
RAISE NOTICE 'The current value of a is %', a;
END first_block $$;

Output:

NOTICE: The current value of counter is 1

PL/pgSQL Sub block:

PL/pgSQL allows you to place a block inside the body of another block. This block nested inside another block is called subblock. The block that contains the subblock is referred to as an outer block.

Syntax:

The sub blocks are used for grouping statements so that a large block can be divided into smaller and more logical sub blocks. The variables in the subblock can have the names as the ones in the outer block, even though it is not a good practice. When you declare a variable within subblock with the same name as the one in the outer block, the variable in the outer block is hidden in the subblock. In case you want to access a variable in the outer block, you use block label to qualify its name as shown in the following example:

Input:

DO $$
<>
DECLARE
a integer := 0;
BEGIN
a := a + 1;
RAISE NOTICE 'The current value of a is %', a;
DECLARE
a integer := 0;
BEGIN
a := a + 10;
RAISE NOTICE 'The current value of a in the subblock is %', a;
RAISE NOTICE 'The current value of a in the outer block is %', outer_block.a;
END;
RAISE NOTICE 'The current value of a in the outer block is %', a;
END outer_block $$;

Output:

NOTICE: The current value of a is 1
NOTICE: The current value of a in the subblock is 10
NOTICE: The current value of a in the outer block is 1
NOTICE: The current value of a in the outer block is 1