|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.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 [ 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.
All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless double-quoted.
There are two types of comments in PL/pgSQL. A double
--) 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
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
END for grouping statements in
PL/pgSQL with the database commands for transaction
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.7.5 Trapping Errors.
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|