|PostgreSQL 7.4.30 Documentation|
|Prev||Fast Backward||Chapter 37. PL/pgSQL - SQL Procedural Language||Fast Forward||Next|
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.
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:
... is perfectly legal code.