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:

Block allow the use of mixed upper and lower case letters

The following example shows the validity of mixed case. This function will display Hello World! .

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';
Note Mixed Case
 

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!".

Terminate statements and variables with a semi-colon

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.

Ability to span over several lines

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.

Allows sub-blocks

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;
       

BEGIN/END keyword

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.

Variable default value and scope

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.