| 41.2. Structure ofPL/pgSQL | ||||
|---|---|---|---|---|
| Prev | Up | Chapter 41. PL/pgSQL —SQL Procedural Language | Home | Next |
41.2. Structure ofPL/pgSQL#
Functions written inPL/pgSQL are defined to the server by executingCREATE FUNCTION commands. Such a command would normally look like, say,
CREATE FUNCTION somefunc(integer, text) RETURNS integerAS 'function body text'LANGUAGE plpgsql; The function body is simply a string literal so far asCREATE FUNCTION is concerned. It is often helpful to use dollar quoting (seeSection 4.1.2.4) to write the function body, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function body must be escaped by doubling them. Almost all the examples in this chapter use dollar-quoted literals for their function bodies.
PL/pgSQL is a block-structured language. The complete text of a function body must be ablock. A block is defined as:
[ <<label>>][ DECLAREdeclarations]BEGINstatementsEND [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 afterEND, as shown above; however the finalEND that concludes a function body does not require a semicolon.
Tip
A common mistake is to write a semicolon immediately afterBEGIN. This is incorrect and will result in a syntax error.
Alabel is only needed if you want to identify the block for use in anEXIT statement, or to qualify the names of the variables declared in the block. If a label is given afterEND, 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 inPL/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 asubblock. 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 anyPL/pgSQL function. This block provides the declarations of the function's parameters (if any), as well as some special variables such asFOUND (seeSection 41.5.5). 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 ofBEGIN/END for grouping statements inPL/pgSQL with the similarly-named SQL commands for transaction control.PL/pgSQL'sBEGIN/END are only for grouping; they do not start or end a transaction. SeeSection 41.8 for information on managing transactions inPL/pgSQL. Also, a block containing anEXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that seeSection 41.6.8.