Code Blocks

PL/pgSQL code is organized in blocks of code. This method of organization is known as block structured code . Code blocks are entered within a SQL CREATE FUNCTION call that creates the PL/pgSQL function in the PostgreSQL database. This CREATE FUNCTION command names the new function, states its argument types, and states the return type. The function's main code block then starts with a declaration section.

All variables are declared and optionally initialized to a default value in the declaration section of a code block. A variable declaration specifies the variable's name and type. The declaration section is denoted by the DECLARE keyword. Each variable declaration is ended with a semicolon.

After declaring variables, the main body of the code block is started with the BEGIN keyword. The code block's statements should appear after the BEGIN keyword.

The END keyword designates the end of the code block. The main block of a PL/pgSQL function should return a value of its specified return type and end any sub-blocks (code blocks started within another code block) before its END keyword is reached.

Example 11-5 shows the structure of a PL/pgSQL code block.

CREATE FUNCTION 
identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
declaration;

    [...]
  BEGIN
    
statement;

    [...]
  END;
' LANGUAGE 'plpgsql';

A block of PL/pgSQL code can contain an unlimited amount of sub-blocks , which are code blocks nested within other code blocks. Sub-blocks are read and interpreted in the same manner as normal blocks; hence, they may also contain sub-blocks of their own.

Sub-blocks can be useful for the organization of code within a large PL/pgSQL function. All sub-blocks must follow normal block structure, meaning they must start with the DECLARE keyword, followed by the BEGIN keyword and a body of statements, then end with the END keyword.