PostgreSQL – Block Structure
PL/pgSQL is a block-structured language, therefore, a PL/pgSQL function or store procedure is organized into blocks.
Syntax: [ <<label>> ] [ DECLARE declarations ] BEGIN statements; ... END [ label ];
Let’s analyze the above syntax:
- Each block has two sections: declaration and body. The declaration section is optional while the body section is required. The block is ended with a semicolon (;) after the END keyword.
- A block may have an optional label located at the beginning and at the end. You use the block label in case you want to specify it in the EXIT statement of the block body or if 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.
Example:
The following example illustrates a very simple block, also known as an anonymous block.
DO $$ <<first_block>> DECLARE counter integer := 0; BEGIN counter := counter + 1; RAISE NOTICE 'The current value of counter is %', counter; END first_block $$;
NOTICE: The current value of counter is 1
The pgAdmin execution of the above block can be done by clicking on the execute button on the interface as depicted below:
Note: 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.
In the declaration section, we declared a variable counter and set its value to zero. Inside the body section, we increased the value of the counter to one and output its value using the RAISE NOTICE statement. The first_block label is just for demonstration purposes.
Concept of Double Dollar($$):
The double dollar ($$) is a substitution of a single quote (‘). When you develop a PL/pgSQL block, a function, or a stored procedure, you have to pass its body in the form of a string literal. In addition, you have to escape all single quote (‘) in the body as follows:
DO '<<first_block>> DECLARE counter integer := 0; BEGIN counter := counter + 1; RAISE NOTICE ''The current value of counter is %'', counter; END first_block';
If you use the double dollar ($$) you can avoid quoting issues. You can also use a token between $$ like $function$ or $procedure$.
PL/pgSQL Subblock:
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.
The subblocks are used for grouping statements so that a large block can be divided 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.
When you declare a variable within a 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 a block label to qualify its name.
Example:
DO $$ <<outer_block>> DECLARE counter integer := 0; BEGIN counter := counter + 1; RAISE NOTICE 'The current value of counter is %', counter; DECLARE counter integer := 0; BEGIN counter := counter + 10; RAISE NOTICE 'The current value of counter in the subblock is %', counter; RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter; END; RAISE NOTICE 'The current value of counter in the outer block is %', counter; END outer_block $$;
Output:
NOTICE: The current value of counter is 1 NOTICE: The current value of counter in the subblock is 10 NOTICE: The current value of counter in the outer block is 1 NOTICE: The current value of counter in the outer block is 1
Let’s analyze the above example:
- First, we declared a variable named counter in the outer_block.
- Then, before entering into the subblock, the value of the counter is one. In the subblock, we increased the value of the counter to ten and print it out. Notice that the change only affects the counter variable in the subblock.
- After that, we referred to the counter variable in the outer block using the block label to qualify its name outer_block.counter
- Finally, we printed out the value of the counter variable in the outer block, its value remains intact.
Contact Us