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.
Free Sample Chapters