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:
- 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
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!
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...