43.2. Structure of PL/pgSQL

43.2. Structure of PL/pgSQL
Prev	Up	Chapter 43. PL/pgSQL — SQL Procedural Language	Home	Next

43.2. Structure of PL/pgSQL #

+ Functions written in PL/pgSQL are defined + to the server by executing CREATE FUNCTION commands. + Such a command would normally look like, say, +

+CREATE FUNCTION somefunc(integer, text) RETURNS integer
+AS 'function body text'
+LANGUAGE plpgsql;
+

+ The function body is simply a string literal so far as CREATE + FUNCTION is concerned. It is often helpful to use dollar quoting + (see Section 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 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. +

Tip

+ A common mistake is to write a semicolon immediately after + BEGIN. This is incorrect and will result in a syntax error. +

+ A label is only needed if you want to + identify the block for use + in an EXIT statement, or to qualify the names of the + variables declared in the block. If a label is given after + END, 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 in PL/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 a subblock. 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 any PL/pgSQL function. This block provides the + declarations of the function's parameters (if any), as well as some + special variables such as FOUND (see + Section 43.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 of + BEGIN/END for grouping statements in + PL/pgSQL with the similarly-named SQL commands + for transaction + control. PL/pgSQL's BEGIN/END + are only for grouping; they do not start or end a transaction. + See Section 43.8 for information on managing + transactions in PL/pgSQL. + Also, 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 43.6.8. +

Prev	Up	Next
43.1. Overview	Home	43.3. Declarations