|PostgreSQL 8.1.23 Documentation|
|Prev||Fast Backward||Chapter 36. 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 [ 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 END that 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 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 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 36.7.5.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.