August 4, 2023
Summary: in this tutorial, you will learn about the block structure of PL/pgSQL and how to write and execute your first PL/pgSQL block.
The following illustrates the syntax of a complete block in PL/pgSQL:
[ <<label>> ] [ declare declarations ] begin statements; ... end [ label ];
Let’s examine the block structure in more detail:
- Each block has two sections: declaration and body. The declaration section is optional while the body section is required. A block is ended with a semicolon (
;) after the
- A block may have an optional label located at the beginning and at the end. You use the block label when you want to specify it in the
EXITstatement of the block body or when you want to qualify the names of variables declared in the block.
- The declaration section is where you declare all variables used within the body section. Each statement in the declaration section is terminated with a semicolon (
- The body section is where you place the code. Each statement in the body section is also terminated with a semicolon (;).
PL/pgSQL block structure example
The following example illustrates a very simple block. It is called an anonymous block.
do $$ <<first_block>> declare film_count integer := 0; begin -- get the number of films select count(*) into film_count from film; -- display a message raise notice 'The number of films is %', film_count; end first_block $$;
NOTICE: The current value of counter is 1
To execute a block from pgAdmin, you click the Execute button as shown in the following picture:
Notice that the
DO statement does not belong to the block. It is used to execute an anonymous block. PostgreSQL introduced the
DO statement since version 9.0.
The anonymous block has to be surrounded in single quotes like this:
'<<first_block>> declare film_count integer := 0; begin -- get the number of films select count(*) into film_count from film; -- display a message raise notice ''The number of films is %'', film_count; end first_block';
However, we used the dollar-quoted string constant syntax to make it more readable.
In the declaration section, we declared a variable
film_count and set its value to zero.
film_count integer := 0;
Inside the body section, we used a
select into statement with the
count() function to get the number of films from the
film table and assign the result to the
select count(*) into film_count from film;
After that, we showed a message using
raise notice 'The number of films is %', film_count;
% is a placeholder that is replaced by the content of the
Note that the
first_block label is just for demonstration purposes. It does nothing in this example.
PL/pgSQL allows you to place a block inside the body of another block.
The block nested inside another block is called a subblock. The block that contains the subblock is referred to as an outer block.
The following picture illustrates the outerblock and subblock:
Typically, you divide a large block into smaller and more logical subblocks. The variables in the subblock can have the names as the ones in the outer block, even though it is not a good practice.
- PL/pgSQL is a blocked-structure language. It organize a program into blocks.
- A block contains two parts: declaration and body. The declaration part is optional while the body part is mandatory.
- Blocks can be nested. A nested block is a block placed inside the body of another block.