This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3

37.3. Structure of PL/pgSQL

PL/pgSQL is a block-structured language. The complete text of a function definition must be a block. A block is defined as:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END;

Each declaration and each statement within a block is terminated by a semicolon.

All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lower-case unless double-quoted.

There are two types of comments in PL/pgSQL. A double dash (--) starts a comment that extends to the end of the line. A /* starts a block comment that extends to the next occurrence of */. Block comments cannot be nested, but double dash comments can be enclosed into a block comment and a double dash can hide the block comment delimiters /* and */.

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.

The variables declared in the declarations section preceding a block are initialized to their default values every time the block is entered, not only once per function call. For example:

CREATE FUNCTION somefunc() RETURNS integer AS '
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE ''Quantity here is %'', quantity;  -- Quantity here is 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE ''Quantity here is %'', quantity;  -- Quantity here is 80
    END;

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

    RETURN quantity;
END;
' LANGUAGE plpgsql;

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start or commit transactions, since PostgreSQL does not have nested transactions.

Comments


Jan. 27, 2005, 11:47 p.m.

It's worth mentioning that a DECLARE section can be empty.

Some languages (eg. Ada) require the use of the keyword NULL in (any) empty block to alert the compiler that the presence of an empty block is intentional.

plpgsql DOES allow empty DECLARE blocks, and empty blocks thereafter, viz:

...
DECLARE
BEGIN
END;
...

... is perfectly legal code.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group