PL/pgSQL Block Structure

Summary: in this tutorial, you will learn about the block structure of PL/pgSQL. You will write and execute the first PL/pgSQL block.

A PL/pgSQL function is organized into blocks.  The following illustrates the syntax of a complete block in PL/pgSQL:

Let’s examine the block structure in more detail:

• Each block has two sections called 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 optional labels at the beginning and at the end. The label at the beginning and at the end must be the same. The block label is used in case you want to use the block in EXIT statement or 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 put the logic of the block. It contains any valid statements. 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.

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.

In the declaration section, we declared a variable named counter and set its value to 0. Inside the body section, we increased the counter to 1 and output its value using RAISE NOTICE statement. The first_block label is just for demonstration purpose. It does nothing in this example.

PL/pgSQL Subblock

You can put a block inside the body of another block. This block nested inside another is called subblock. The block that contains the subblock is referred to as an outer block.

You often use subblocks 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 define 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; see the following example:

In this example, we first declared a variable named counter in the outer_block.

Next, in the subblock, we also declared a variable with the same name.

Then, before entering into the subblock, the value of counter is 1. In the subblock, we increased the value of the counter to 10 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 block label to qualify its name outer_block.counter.

Finally, we print out the value counter variable in the outer block, its value remains intact.

In this tutorial, you have learned about the block structure of PL/pgSQL and how to execute a block using DO statement.