PL/pgSQL Block Structure

PL/pgSQL is the procedural language for the PostgreSQL database that supports the concept of block structuring. A "block" in Postgres refers to a data storage building block where information can be stored. In simple terms, it is just like a container that can keep a certain amount of data. It has a crucial/vital role in storing and manipulating Postgres database data.

In this post, we’ll understand the structure of the PL/pgSQL language. We will also learn to write the PL/pgSQL block. Let’s get started.

PL/pgSQL Block Structure

PL/pgSQL is a block-structured language. Therefore, the PL/pgSQL functions are organized into block structures. But what does a block look like?

Following is a basic syntax of a complete block in PL/pgSQL:

[ <<label>> ]
   [ DECLARE
  Write declarations here]
 BEGIN
  Write your statements/code/queries here.
 END [ label ];

Let’s have a look at the main components of a block:

Every block is divided into two main sections: Declaration and body. The declaration section is completely optional to write. Whereas it is compulsory to write the body in the block structure. The variables are declared in the declaration section which are used within the body and the body is where you write your code. Each statement in both sections is terminated by semicolons.

One thing is to be noted here, the block ends where there is a semicolon after the END statement. Also, a block may contain an optional label at the beginning and end. The label has to be the same in both places.

Example

Below is a very simple example illustrating the basic syntax of PL/pgSQL Block Structure:

DO $$
   <<first_block>>
 DECLARE
  stud_count integer := 0;
 BEGIN
  --   Get the number of Students in a class
  SELECT count(*)
  INTO stud_count
  FROM test_scores;
  --   display a message
  RAISE NOTICE 'The number of Students is %', stud_count;
 END first_block   $$;

In the above example, the block starts from the <<first_block>> label and ends at the semicolon after the END statement. In the declare section, the variable, stud_count is declared as zero.

The body section starts from the BEGIN statement, where the whole code is placed. Here in the body, we have used SELECT INTO with count() function to get the number of students from the table Students and assign the value to the variable i.e. student_count.

The RAISE NOTICE is used to show the message. Here the essential point to mention is that do is not part of the block. It is just there to execute/run an anonymous block of code.

The output of this query is the number of students present in the table “test_score”. The output looks like this:

We can see that the query has not shown the initially declared value of the stud_count rather it entered the block to print the count value from the table.

PL/pgSQL Sub-block Structure

PL/pgSQL also supports a sub-block structure. We can place another block inside a block. The block placed/nested inside a block is called a sub-block. The block that possesses/contains the subblocks is called the outer block. So, the basic concept is the outer block is divided into smaller but more logical sub-blocks.

Below is the basic syntax showing a sub-block and an outer block:

[ <<label>> ]
  [ DECLARE
  Write declarations here]
 BEGIN
  Write your statements/ queries here.
  --
  -- Create a subblock
  --
  [ DECLARE
  Write declarations here ]
  BEGIN
  Write your statements/queries here
  END;
 END [ label ];

A sub-block is created, this will also contain both sections i.e. declaration and the body similar to the outer block. We’ll implement the sub-block structure of PL/pgSQL in the following example.

Example: Understanding the Sub-Block Structure of PL/pgSQL

Let’s understand the sub-block structure of the PL/pgSQL using the following query, which keeps the student count. The query is given below:

DO $$ 
   <<outer_block>>
 DECLARE
  stud_count int := 0;
 BEGIN 
  stud_count := stud_count + 10;
  RAISE NOTICE 'The current count of   students is %', stud_count;
 -- the inner block/sub-block starts from   here
 DECLARE 
  stud_count int := 100;
  BEGIN 
  stud_count := stud_count + 20;
  RAISE NOTICE 'The current count of students in the subblock is %', stud_count;
  RAISE NOTICE 'The current count of students in the outer block is %', outer_block.stud_count;
  END;
-- the inner block/sub-block ends here
RAISE NOTICE 'The current count of students in the outer block is %', stud_count;
END outer_block $$;

In the above code;

● There is an inner/ sub-block in the “outer_block” that starts from the inner DECLARE statement and ends at the inner END statement.

● We have declared the variable “stud_count” as 0 in the outer block. After the BEGIN keyword, we have added the integer 10 in the “stud_count” and raised it as a NOTICE.

● The inner block gives the “stud_count” variable the value of 100.

● We have raised a NOTICE of “stud_count” + 20 in the inner block, we will get 120 in return. Here the value of “stud_count” is 100.

● Now if we want to access the outer-block value of the “stud_count”. We will have to access it by the dot operator i.e. “outer_block.stud_count”. This gives us the current value from the outer block.

● After the END keyword, the inner block is ended. After that, everything lies in the outer block.

● The last RAISE NOTICE statement returns the current value of “stud_count” in the outer block.

The above description can easily be understood by following the below output.

This is how the sub-block structure works in PL/pgSQL.

Conclusion

PL/pgSQL supports block structure. The blocks start from the DECLARE statement till the END statement. Remember that the do statement is not included in the block. The block has two sections; a declaration and a body. Sub-block structure is also supported by PL/pgSQL, which means that we can create a sub-block in an outer block. In this article, we have learned about the structure of PL/pgSQL structure and how it is implemented using examples.

PL/pgSQL Block Structure