|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.2 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 [ 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
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
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 9.5.5 Obtaining the Result Status). 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
END for grouping statements in
PL/pgSQL with the similarly-named SQL commands
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 that
transaction, since there would be no context for them to execute in.
However, 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 9.6.5 Trapping Errors.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|