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
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.
[ <<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.
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.
- 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 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.
- 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!