PL/pgSQL is a structured language designed around blocks. This means that variables declared in a block can not be used after the closing of the code block. In other words, if you have a variable within a block and you close the code block, the variables value will be destroyed. When writing code, PL/pgSQL requires that related code statements to be grouped together in a block.
A code block is initialized with a declaration. The declaration section contain the variables for use within the code block. Then the body of the block is indicated by the keyword BEGIN . The code block is then terminated by the END keyword and a semi colon (;).
Here is an example of a block structure:
[<<label>>] DECLARE declarations ; BEGIN statements ; END;
Within the BEGIN statement are the instructions that tell the function what to do. It can have several different formats, depending on how you would like the function to execute. Statements can only be used in the body of a block.
Here are some general rules to keep in mind when dealing with blocks:
Example 9-2. Mixed case PL/pgSQL Block
CREATE FUNCTION mixedcase() RETURNS text AS' DecLarE -- DeCLaRes A vArIAblE to hOLd tHe STrInG pHrAsE tExT; bEGiN -- pUTs hELlo WoRLd! IntO tHe pHrAsE pHrAsE = ''Hello World!''; -- dISplAys thE pHrAsE ReTUrn pHrAsE; eNd; ' LANGUAGE 'plpgsql';
No sane programmer would code in this manner.
To maintain proper coding style, keywords such as DECLARE , BEGIN , and END should be written in all capital letters. Variables such as pHrAsE should be in lower case. However, some programmers may prefer variables names such as myPhrase . When using variables remember that the value of the variable is literal. If you assign the value of pHrAsE to equal "HELLO WORLD!" it will not be the same as assigning the value of pHrAsE to "Hello World!".
All block statements and declarations are required to terminate with a semi-colon(;). The only exception are comments. There is a different syntax to end a block or multiple line comment. Please refer to the comments section for more details.
Block statements and declarations can span over several lines. It is helpful to the eye if you split a long statement over multiple lines. Just make sure that you end the statement with a semicolon. Using the previously defined function which displays Hello World! , we split some of the commands over several lines:
Example 9-3. Spanning Over Multiple Lines in PL/pgSQL
CREATE FUNCTION first() RETURNS text AS ' DECLARE -- declares a variable to hold the phrase phrase text; BEGIN -- puts Hello World! into the phrase phrase = ''Hello World!''; -- displays the phrase return phrase; END; ' LANGUAGE 'plpgsql';
Usually, short commands like the ones in the previous function example do not need to be split over several lines. If you plan to split advanced commands over several lines, make sure that you are not splitting words inside of quotes, command names, or single line comments. If you do so, the meaning of the code will change and it may not perform the correct calculations.
Any number of sub-blocks in the statement section is allowed. Make sure that you always have a corresponding END to each BEGIN keyword. This function body has some comments about the function body structure:
Example 9-4. Sub blocks in PL/pgSQL Block
DECLARE -- variables are declared here BEGIN -- outer block body DECLARE -- sub block declarations BEGIN -- sub block body -- end of sub block END; -- end of outer block END;
The BEGIN and END keywords are for grouping purposes only. Do not confuse these keywords with the PostgreSQL keywords to start or commit a transaction. In PL/pgSQL, these kewords start and end the block body.
Variables defined in the declarations section are initialized to their default value each time the block is entered. This default value is usually NULL , unless the user has assigned a value to the variable. You can change the default value later by using an assignment statement. For more information, see the assignment section.
These declarations are local to the block. When the end of the block is reached, these variables no longer exist. For example, if you create a variable to hold a table row in the block, that table row is automatically closed at the end of the block.