How to use block structure to write anonymous blocks and divide larger blocks into logical subblocks

Linux x86-64 (RHEL 8)

Tushar Ahuja Sr. QA Manager

SUMMARY: This article discusses block structure in PostgreSQL, how to write an anonymous block, and how to divide a larger block into logical subblocks. 

1. Block structure 

2. Anonymous blocks in PL/pgSQL

3. Subblocks

Block structure 

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural language where you can perform more complex tasks than in SQL—like easy computation—and also make use of loops, functions, and triggers. PL/pgSQL code is managed in blocks (block structured code), into which anonymous blocks, functions, and procedures are organized.

Syntax

[ <<label>> ]

[ DECLARE

    declarations ]

BEGIN

    Statements

[EXCEPTION

    Exception handler ]  

END [ label ];

 

Let’s look at the syntax in detail: 

  1. Every block has 3 sections: 
    • The DECLARE section (which is optional), where we can declare all the variables (e.g., abc varchar(10);) that we want to use in the BEGIN block.
    • The BEGIN section (which is mandatory), where we can perform some action (e.g., abc:=10;) 
    • The EXCEPTION section (which is optional), where we can write a handle for catching errors in runtime.
  2. END: This keyword closes the block.
  3. Label:  This is an optional keyword used to identify the anonymous block, in case of an  EXIT statement or if we need to qualify the variables names that are declared in the block. The label given before DECLARE/BEGIN should match with the level given after the END keyword. 
  4. Functions and procedures are defined in the BEGIN section of a block. 

More information on the block structure of PL/pgSQL can be found in the PostgreSQL online documentation: https://www.postgresql.org/docs/11/plpgsql-structure.html.

 

Anonymous blocks in PL/pgSQL

The DO statement executes an anonymous code block. PostgreSQL started supporting anonymous blocks with version 9.0. 

Syntax

DO [ LANGUAGE lang_name ] code

 

  • Here “code” can be considered as the body of a function with no parameters, which is going to return void and be parsed and executed one time only (i.e., not going to be stored in database catalog).
  • “lang_name”  is simply the name of the procedural language. If the language is not mentioned, PostgreSQL will use the default procedural language, PL/pgSQL.
  • $$ (double quoting) is a PostgreSQL substitute for single quotes to avoid quoting issues inside the BEGIN block.

Examples

  • Showing a message:
postgres=#do

                 $$

                 <<main_block>>

                 begin

                     raise notice 'Hello World, I am an anonymous block';

                 end main_block;

                 $$;

                 NOTICE:  Hello World, I am an anonymous block

                DO  

 

  •  Performing some calculation and assigning the result to some other variable:
postgres=#Do 

             $$

              Declare

                 abc int;

                 xyz int;

             Begin

                 abc:=100;

                 xyz:=abc/50;

                 raise notice '%',xyz;

             end;

               $$;

                       NOTICE:  2

                       DO

 

  • Raising a user-defined message instead of an error message using EXCEPTION: 

   

     postgres=#do

$$

Declare

    abc int;

    xyz int;

Begin

    abc:=100;

    xyz:=abc/0;

Exception

    WHEN  division_by_zero THEN

        raise notice 'can not divide by zero';

End;

$$;

                      NOTICE:  cannot divide by zero                                                  

                      DO

 

  • Calling function from anonymous block: 
postgres=# create or replace function f(n int) 

           returns int as $$ 

           begin n:=5; 

               return n; 

           end; 

           $$language 'plpgsql';

           CREATE FUNCTION

postgres=#do

          $$

          declare

              abc int;

              xyz int;

          begin

              abc:=f(5)*10; 

              raise notice '%',abc;

          end;

          $$;

         NOTICE:  50

         DO

 

Subblocks

Subblocks are used for logical groupings of a small group of multiple statements. In other words, we can have a block inside another block. The block that is nested inside another block is called subblock (or inner block), and the block that contains the subblock called outer block.

Examples

  • The outer block variable is visible from the inner block:
postgres=#do 

                  $$

                <<outer_block>>

                  declare           -- Outer Block--

                      a int;

                  begin

                      a:=1;

                    <<inner_block>>

                      declare       -- Inner Block--

                          b int;

                      begin

                          b:=a;      --Inner block can read the values from outer block--

                          raise notice '%',b;

                      End inner_block;

                  End outer_block;

                  $$;

               NOTICE:  1

               DO

 

In this example, the value of the variable that is declared in the outer block is visible to the inner block.

  • The outer block cannot see the variable value that is declared in the inner block: 
postgres=#do 

                  $$

                 declare         -- Outer block--

                      a int;

                 Begin 

                      a:=1;

                      declare    -- Inner block --

                           b int;

                      begin

                           b:=1;

                      End;        -- Inner block closed--

                     a:=b;        -- assigning  inner block variable value  inside  outer block--

                     raise notice '%',a;

                 end;

                 $$;

                 ERROR:  column "b" does not exist

 

In this example, the variable “b” from the inner block is not accessible to the outer block and throws an error.

Hope it helps! 

 

Tushar AhujaSr. QA Manager