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

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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:

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.

END: This keyword closes the block.

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.

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
   
    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
    
     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;
                 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!
    
    
     
      Connecting PostgreSQL using psql and pgAdmin
     
    
    
     
      How to use PostgreSQL with Django
     
    
    
     
      10 Examples of PostgreSQL Stored Procedures
     
    
    
     
      How to use PostgreSQL with Laravel
     
    
    
     
      How to use tables and column aliases...
     
    
    
     
      PostgreSQL vs. SQL Server (MSSQL)...
     
    
    
     
      The Complete Oracle to PostgreSQL Migration...
     
    
    
     
      PostgreSQL vs. MySQL: A 360-degree Comparison...
     
    
    
     
      PostgreSQL Replication and Automatic Failover...
     
    
    
     
      Postgres on Kubernetes or VMs: A Guide...
     
    
    SUMMARY: This article reviews five ways to sort table data using the ORDER BY clause together with the SELECT statement in PostgreSQL.
1. Sorting by column...
    
     January 24, 2023