41.2. Structure of PL/pgSQL #

   Functions written in PL/pgSQL are defined to the server by executing
   CREATE FUNCTION commands. Such a command would normally look like, say,
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

   The function body is simply a string literal so far as CREATE FUNCTION
   is concerned. It is often helpful to use dollar quoting (see
   Section 4.1.2.4) to write the function body, rather than the normal
   single quote syntax. Without dollar quoting, any single quotes or
   backslashes in the function body must be escaped by doubling them.
   Almost all the examples in this chapter use dollar-quoted literals for
   their function bodies.

   PL/pgSQL is a block-structured language. The complete text of a
   function body must be a block. A block is defined as:
[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

   Each declaration and each statement within a block is terminated by a
   semicolon. A block that appears within another block must have a
   semicolon after END, as shown above; however the final END that
   concludes a function body does not require a semicolon.

Tip

   A common mistake is to write a semicolon immediately after BEGIN. This
   is incorrect and will result in a syntax error.

   A label is only needed if you want to identify the block for use in an
   EXIT statement, or to qualify the names of the variables declared in
   the block. If a label is given after END, it must match the label at
   the block's beginning.

   All key words are case-insensitive. Identifiers are implicitly
   converted to lower case unless double-quoted, just as they are in
   ordinary SQL commands.

   Comments work the same way in PL/pgSQL code as in ordinary SQL. A
   double dash (--) starts a comment that extends to the end of the line.
   A /* starts a block comment that extends to the matching occurrence of
   */. Block comments nest.

   Any statement in the statement section of a block can be a subblock.
   Subblocks can be used for logical grouping or to localize variables to
   a small group of statements. Variables declared in a subblock mask any
   similarly-named variables of outer blocks for the duration of the
   subblock; but you can access the outer variables anyway if you qualify
   their names with their block's label. For example:
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints
 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

Note

   There is actually a hidden “outer block” surrounding the body of any
   PL/pgSQL function. This block provides the declarations of the
   function's parameters (if any), as well as some special variables such
   as FOUND (see Section 41.5.5). The outer block is labeled with the
   function's name, meaning that parameters and special variables can be
   qualified with the function's name.

   It is important not to confuse the use of BEGIN/END for grouping
   statements in PL/pgSQL with the similarly-named SQL commands for
   transaction control. PL/pgSQL's BEGIN/END are only for grouping; they
   do not start or end a transaction. See Section 41.8 for information on
   managing transactions in PL/pgSQL. Also, a block containing an
   EXCEPTION clause effectively forms a subtransaction that can be rolled
   back without affecting the outer transaction. For more about that see
   Section 41.6.8.