Creating Functions

Code Blocks

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:

Comments

Commenting in PL/pgSQL is done through two methods. The first method is a single line comment. These comments begin with two dashes (--) to express the line is a comment. The second type of comment is called a block comment. It begins with a forward slash asterisk (/*) and ends with an asterisk and another forward slash (*/). It is capable of spanning over multiple lines. Unlike PostgreSQL comments, block comments cannot be nested, but single line comments can. For example:

 
-- This is a single line comment
/* This block comment spans 
*  over multiple 
*  lines /* this nested comment is illegal*/
-- this nested comment is valid
*  because it is inside of a block comment*/
   

Like in any programming language, it is helpful if you write good comments. A comment can be considered good if it can express to the user why you programmed it the way it is. Comments that restate what you are doing are not anymore helpful to the user. For instance, the comments in this function are irrelevant to why it is doing things the way it is:

   CREATE FUNCTION mult_two(integer) RETURNS integer AS'
   DECLARE   
     -- declares an input1 variable 
     input1 ALIAS FOR $1;
   
     -- declares a variable
     answer integer;
   BEGIN
     -- assigns to the answer 
     -- the result of input1 times 2 
     answer = input1 * 2;
 
     -- returns the variable
     return answer;
   END;
   ' LANGUAGE 'plpgsql';
   

Instead, you should make comments like these:

   CREATE FUNCTION mult_two(integer) RETURNS integer AS'
   DECLARE   
     -- defines a name for the first input 
     input1 ALIAS FOR $1;
   
     -- a variable to hold the answer
     answer integer;
   BEGIN
     -- multiples the input number by 2
     -- and stores it in the answer 
     answer = input1 * 2;
 
     -- displays the answer
     return answer;
   END;
   ' LANGUAGE 'plpgsql';

Comments in this part will have a little of both styles because we are not only showing you why but also what we are doing in the code.

Declarations

Variables are used to store and manipulate data. Variables are always defined in the declarations section of a block before it is used elsewhere in the block body. The declarations section is the portion beneath where you specified the keyword DECLARE. The possible data types a variable can be declared with are any valid SQL data type and a few other data types special to PL/pgSQL.

The following list some items that must be declared and examples of how to declare them:

As you may have noticed, each variable declared must be terminated with a semi-colon (;) and the variable name is listed first, then comes the data type of the variable.

The only variable that does not need to be declared is the incrementing variable used by the FOR Loop. This incrementing variable is set to increase a number by 1 every time the a statement loop is entered. See the Iteration section for more information on FOR Loops.

You can declare variables at the beginning of a block even though you may be planning to use the variable later in a sub block. This way, the variable can be visible to the entire function, instead of just to the sub-block. The range where the variable is visible is called the scope.

The next example shows how the scope is in violation. It first declares a phrase variable in a sub-block to hold a string. It then assigns Hello World! to the variable and exits the sub-block. Now that it is in the main block body, it tries to return the phrase. This will not work because the phrase is not visible outside of the sub-block.

CREATE FUNCTION scope_var() RETURNS text AS'
DECLARE
-- nothing is declared here

BEGIN

  DECLARE
     -- declares a string phrase
     phrase text;

  BEGIN
     -- assigns Hello World! to the phrase
     phrase = ''Hello World!'';

  -- end of the phrase's scope
  END;

-- this creates an ERROR!
return phrase;
END;
' LANGUAGE 'plpgsql';
  

To fix this problem, you can declare the phrase in the outer declarations section. Then add the Hello World! string to the phrase and display the result:

CREATE FUNCTION scope_var() RETURNS text AS'
DECLARE
  -- declares a string phrase
  phrase text;
BEGIN

  DECLARE
  -- nothing is declared here   

  BEGIN
     -- assigns Hello World! to the phrase
     phrase = ''Hello World!'';

  -- jumps out of the sub block
  END;

-- displays the phrase 
return phrase;
END;
' LANGUAGE 'plpgsql';
   

	Escaping Single Quotes
	When the Hello World! string is put into the phrase variable, the string is inside a set of two single quotes. The first single quote escapes the inside pair of quotes. Be sure to remember this when you are dealing with strings in PL/pgSQL.

When this function is called with the SQL SELECT statement:

   SELECT scope_var();
   

It displays a temporary scope_var column with the phrase:

 
  scope_var
--------------
 Hello World!
(1 row)
   

Variables

Variables are the names given to objects which have a defined data type. The possible variable data types are any valid SQL data type, such as integer, date, time... etc. Refer to the data types table in the Understanding SQL chapter for more details on valid SQL data types.

This is an example which declares a variable for the author identification number and their firstname:

 DECLARE
   -- defines a variable to hold 
   -- a numeric author identification number
   author_id INTEGER;

   -- defines a variable to hold the first name 
   firstname TEXT; 
 BEGIN

    -- instructions 
    statements;
 END;
   

Sometimes, after having declared a variable, you want to assign a different default value to variables and constants. The syntax is:

 name [CONSTANT] type [NOT NULL] [{DEFAULT |:=}value];
   

The only situation that requires you to assign a default value is when the variable or constant is defined as NOT NULL. Remember, the default value is NULL. When a NOT NULL is specified, the default value is no longer valid. You must assign a new default value for the variable or constant.

These are some examples of assigning a default value to the variables, author_id and firstname:

 DECLARE
   -- assigns number 212 to the author_id variable
   author_id INTEGER := 212;

   -- assigns the name Suess to the firstname variable
   firstname TEXT := "Suess";

 BEGIN
    
   statements;
 END;
   

Assignment

Assignment statements are important to any programming language. They allow the programmer to manipulate the data. Assignment statements can be used to store or transfer the value of an expression into a variable.

An expression is defined as a computation which returns a value. When assignment is used, these expressions are stored by identifier variables. The identifier is the name of a variable. For example, the syntax to use an assignment statement is:

  identifier := expression;
   

The colon and equal sign (:=) in the syntax shows that the expression is being stored by the identifier variable. It is then terminated with a semicolon (;).

Assignment can also be used to specify a value for a variable or row/record field. These are some examples of assignment statements to a variable that has already been declared:

    ...
  BEGIN
    -- assigns 120 to the author_id variable
    author_id := 120;

    -- assigns the result of a multiplication 
    -- into the tax variable
    tax := subtotal * 0.06;
  END;
  

You should keep in mind that if the expression type differs from the variable data type, the PL/pgSQL parser will generate some run time errors.

To call a function, use the execute SELECT query or do an assignment. An assignment will cause an internal PL/pgSQL SELECT to execute. After execution, it will return a value. All functions in PostgreSQL returns a value. If you did not want to return a result from the function call, then specify PERFORM:

 PERFORM query  
   

Returning from a Function

The return statement terminates the execution of the current function and returns to the routine that called the function. You are allowed to use the return keyword elsewhere in the code besides at the end of the function body. For instance, the return keyword can be used at the closing of a function body to display a data value.

Use this syntax to RETURN from a function:

   RETURN expression;
   

For example, this function called avg3() returns the result of an average operation on three numbers:

   CREATE FUNCTION avg3(integer, integer, integer) RETURNS integer AS'
    DECLARE
       -- defines an alias name 
       -- for the three inputs to be calculated
       num1 ALIAS FOR $1;
       num2 ALIAS FOR $2;
       num3 ALIAS FOR $3;

       -- defines an average variable to hold the answer
       avg integer;

    BEGIN
       -- adds the 3 numbers together and divides by 3
       -- assigns the result to the avg variable
       avg := ((num1 + num2 + num3)/3);

       -- displays the average result 
       return avg;

    END;
  ' LANGUAGE 'plpgsql'; 
   

The avg3 () function takes in three numbers and then adds them together and divides by 3 to get the average. It then puts that result into the avg variable.

When this function is invoked, and supplied with the numbers: 21 , 29, and 10, it returns:

 
        avg3
       ------
         20
       (1 row)
       

This is the correct answer, but when supplied with the numbers: 21, 30, and 10, it displays:

        avg3
       ------
         20
       (1 row)
       

How can this be? It is because throughout the entire function, the avg variable and return type is declared as an integer. Therefore, the result of the average operation will always return a whole number, but not the numbers after the decimal point.

To fix this, there are several ways you can approach the problem:

With either method, you will need to change the avg variable and define the function to return a numeric data type. If the second method is used, the function should look like this:

   CREATE FUNCTION avg3(numeric, numeric, numeric) RETURNS numeric AS'
    DECLARE
       -- defines an alias name 
       -- for the three inputs to be calculated
       num1 ALIAS FOR $1;
       num2 ALIAS FOR $2;
       num3 ALIAS FOR $3;

       -- defines an average variable to hold the answer
       avg numeric;

    BEGIN
       -- adds the 3 numbers together and divides by 3
       -- assigns the result to the avg variable
       avg := ((num1 + num2 + num3)/3);

       -- displays the average result 
       return avg;

    END;
  ' LANGUAGE 'plpgsql'; 
   

Once again, try supplying the numbers: 21, 30, and 10. It outputs the correct answer:

        avg3
     ---------------
      20.3333333333
     (1 row)
      

Once this function terminates, the value of the expression is returned to the parser. Therefore, a return value must be defined. If not, a runtime error will occur.

Concatenating

Concatenating means to combine two strings together.

This simple example joins the first and second string of words supplied to become a compound word.

   CREATE FUNCTION compound_word(text, text) RETURNS text AS'
     DECLARE
       -- defines an alias name for the two input values
       word1 ALIAS FOR $1;
       word2 ALIAS FOR $2;
     BEGIN
       -- displays the resulting joined words
       RETURN word1 || word2;
     END;
  ' LANGUAGE 'plpgsql';
   

When the words break and fast are passed as arguments to the compound_word() function, it displays:

 SELECT compound_word('break', 'fast');

 compound_word
---------------
 breakfast
(1 row)
      

	Dealing with Text in PostgreSQL
	When the words break and fast are supplied to the function, they are inside of single quotes because the function takes a text argument. You will recieve an error if any text string is passed without the single quotes.

Renaming

The RENAME keyword allows you to change the name of a variable, record, or row. This is helpful if the NEW or OLD keyword is referenced by another name inside of a trigger procedure. For more details on triggers, refer to the Triggers section.

If you need to change the name of a variable, record, or row, use the rename syntax:

  RENAME oldname TO newname;
   

Remember, rename is defined in the declarations section of a block. Here are two examples:

  
  DECLARE
    RENAME isbn TO book_id;
  BEGIN
    ... 


  DECLARE
    RENAME old TO author_row;
  BEGIN
    ...
   

This function uses the rename keyword to rename a new row to be replaced into the book table. By renaming new to data, it can be called in the body of the function as data.

CREATE FUNCTION rename_new () RETURNS opaque AS '
DECLARE
  -- gives the new trigger variable name
  -- another name, data
  rename new to data;
BEGIN
  -- returns the new row into the book table
  return data;
END;
' LANGUAGE 'plpgsql';
   

For more information on trigger variables, refer to the trigger section.

This trigger invokes that function when an update occurs on the book table:

  CREATE TRIGGER rename_new 
  BEFORE UPDATE on book 
     FOR EACH row 
 EXECUTE PROCEDURE rename_new();
   

Aliases

An alias provides a way to identify the user input. Aliases allow the programmer to refer to a name when referring to a variable passed by a function. A variable passed by a function is a numbered variable that identifies the input.

If you have quite a long list of input arguments, then it can be difficult to distinguish the inputs from one another. You can solve this problem by assigning a variable name to the variable passed by the function. Using aliases to assign a meaningful name will help you to have consistent code, making it easier for you to use advanced coding schemes. Also, it is another way of giving you a uniform naming structure and to create portable code.

Use this syntax to define an alias:

   
name ALIAS FOR $n;
   

The name is the variable name that you would like to give the variable input. The n represents a number 1 or greater and 16 or less.

It is a good idea to keep in mind the following rules listed when using an alias:

• Variables passed to a function which are used by an alias must begin with a dollar sign ($). This is the special character that indicates it is a special variable.

• Variables passed to functions uses numbers ranging from 1 to 16.

• It must be defined in the declarations section of a function block. For instance, the following defines an alias named lastname for the variable $1:

      CREATE FUNCTION customer(text) RETURNS text AS'
       DECLARE   
         lastname ALIAS FOR $1;
       BEGIN
         .... 

Here is an example of a double_age function which references an alias with a variable name and multiplies the input value by two:

 CREATE FUNCTION double_age(integer) RETURNS integer AS '
 DECLARE 
   -- defines an ALIAS name for the assumed_age
   assumed_age ALIAS FOR $1;

 BEGIN
   -- displays the assumed age  
   -- after multiplying it by two 
   return assumed_age * 2;

 END; 
 ' LANGUAGE 'plpgsql';
   

For instance, if we passed into this function the age 14, then it will return the age 28:

   SELECT calc_age(14);

   calc_age
   ----------
         28
   (1 row)
   

	Dotting Aliases
	The dot notation used by SQL functions to specify an alias is not allowed by PL/pgSQL. Usually, this notation has the form: $1.variable However, the dot(.) notation is used elsewhere by PL/pgSQL. For instance, it is used as a reference to columns in a table. See the next section on attributes for more information.

The only time you are required to use an alias is when there are composite types passed as the arguments to a function. In this case, composite types are complete table rows. If a table row is passed into a function, you will need to use an alias name because the input is a whole table row. An alias name can be defined to refer to each column in the table row. You can then use the individual alias names to refer to a column in the function body.

Exception Handling

The RAISE command is provided by PL/pgSQL to give the user the ability to output messages to the PostgreSQL elog mechanism. The elog() function is used to send messages to the front-end. To use the RAISE command, follow this format:

   RAISE level 'format' [, identifier [...]];
   

There is a special % character commonly used as a placeholder for a variable. The variable is then listed after a comma and the % is placed at the desired location where the variable would be. For example, this RAISE EXCEPTION command will print the value held by the user_name variable:

   RAISE NOTICE ''The user % does not exist.'', user_name;
   

If the value of user_name is mark, then the message would say:

    NOTICE: The user mark does not exist.
    

	Using Quotes
	In PostgreSQL, double quotes have a special meaning, so if a function is being defined in PostgreSQL, then the function cannot use double quotes. To print a RAISE message, place the desired message inside a set of two single quotes. One single quote escapes the other quote, which is the actual single quote required to use the RAISE command.

There are different types of levels to print a message. They can be any of:

The following function illustrates the different levels of handling error messages. If a user supplies a number 10 or below, it will use the notice level to print out a message. Anything from 11 to 20 uses the debug mechanism to print a message to the logs. It also prints a notice message because the debug mechanism will not print to the client application. Lastly, numbers greater than 20 use the exception level to terminate the transaction and print a message to the client application.

CREATE FUNCTION raise_msg(integer) RETURNS integer AS'
DECLARE
  -- defines an alias name for the input
  num ALIAS FOR $1;
  
BEGIN
  -- if the number supplied is 10 or less
  IF num <= 10 THEN
  -- prints a notice and displays the number
  RAISE NOTICE ''This notice displays the number %.'', num;

    -- if the number supplied is 20 or less
    ELSE IF num <= 20 THEN
    -- prints a debug message to the logs
    RAISE DEBUG ''This debug sends the number % to the logs.'', num; 
    -- also prints a notice 
    RAISE NOTICE ''The debug message has been sent.'';

    -- if the number supplied is 21 or greater
    ELSE 
    -- prints an exception message and displays the number
    RAISE EXCEPTION ''This exception prints % and ends the transaction.'', num; 
    -- end of else if body  
    END IF;

 -- end of if body
 END IF;

-- displays the number supplied
return num;
END;
' LANGUAGE 'plpgsql';
   

When a 5 was supplied to this function, it returns:

 
NOTICE:  This notice displays the number 5.
 raise_msg
-----------
         5
(1 row)
   

Then an 11 was passed to the function and it displayed:

 
NOTICE:  The debug message has been sent.
 raise_msg
-----------
        11
(1 row)
   

Notice, this message did not say,

    
      "DEBUG: this debug sends the number 11 to the logs." 
       

It is because the DEBUG mechanism only prints to the logs. If you would like to view the DEBUG message, you can perform a tail command on the path /usr/local/pgsql/data/serverlog. Once executed, it should display a list of messages that were generated. The last message should be the DEBUG message:

 
DEBUG:  This debug sends the number 11 to the logs.
   

Lastly, a 55 was given as a parameter and the function answered with the message:

ERROR:  This exception prints 55 and ends the transaction.  
   

The result of the RAISE EXCEPTION command did not printe the number 55 in a column like the other messages. That is because the EXCEPTION level stops the transaction from finishing. Therefore, you only receive a message and it doesn't return the number 55 to the client application.

Expressions

Expressions are calculations which return a resulting value. They can be used to enhance data manipulation. In some cases, expressions are used to limit the returned data from a SELECT statement.

The syntax to use an expression is:

   SELECT expression
   

Expression can be any valid SQL SELECT statement. Refer to Chapter 4 for more information on SELECT statements.

To illustrate, the following function inserts a timestamp into a column of an existing database table by using the now function to get the current time. The time is then stored into a variable that is inserted into a table.

   CREATE FUNCTION date_ship(integer) RETURNS timestamp AS'
      DECLARE
         -- variable name for the invoice number input
         inv_num ALIAS FOR $1;

         -- variable to hold the current time
         curr_time timestamp;

      BEGIN
         -- grabs the current time
            curr_time := ''NOW'';

         -- inserts the current time and invoice number
         -- into the shipped_orders table
         INSERT INTO shipped_orders (invoice_num, ship_date)
                VALUES (inv_num, curr_time);

         -- displays the time inserted into the table
         RETURN curr_time;

      END;
   ' LANGUAGE 'plpgsql';
   

If you use the previously defined date_ship function and specify to add invoice number 202:

   SELECT date_ship(202);
   

Then it displays this:

  date_ship
------------------------
 2001-07-07 12:41:06-07
(1 row)
   

The shipped_orders table should also have a row with those values inserted:

 invoice_num | subtotal | cust_id | ship_date
-------------+----------+---------+------------
         272 |   $13.50 |      28 | 2001-02-15
         280 |   $11.75 |      21 | 2001-03-01
         291 |   $16.99 |      10 | 2001-04-20
         273 |   $14.25 |      10 | 2001-04-19
         274 |   $26.97 |      10 | 2001-05-15
         275 |   $41.35 |      28 | 2001-03-09
         201 |   $35.00 |      15 | 2001-07-06
         202 |          |         | 2001-07-07
(8 rows)
   

Notice that the newly added row does not have the time stamped into the row. That is because the column is a date data type, not timestamp. Therefore, when dealing with tables, the constraints on the table still applies. For more information on constraints, refer to the section called Constraints in Chapter 5.

	Statement errors
	Any type of statement not understood by the PL/pgSQL parser is still put in a query and sent to the database for execution. Once executed, the result will return nothing.

Attributes

An attribute is a characteristic that can be used to further define a variable. There are two attributes provided to declare variables with the same structure as a database table or view. They are the %TYPE and %ROWTYPE attributes.

The %TYPE attribute specifies that the variable has the same structure as a database column. The advantage of using this attribute is that it does not need to know before hand the data type of the database object you are referencing. If the data type changes, then you do not need to change the function definition. For instance, the book table contains the isbn column. To reference that column, use the command:

 isbn book.isbn%TYPE;
   

As you can see, the dot(.) operator is used to refer to the column in the book table. The columns and tables must exist in the database for this to work. If the columns and tables do not exist, then it cannot build a column or table with the same structure. Again, the syntax to use the %TYPE attribute is:

   varName table.column%TYPE;
   

	The %TYPE and %ROWTYPE attribute
	Note that declaring a variable with the structure of a column or table does not mean that the variable now holds the data values in the column or table. To put the same values in the column or table into the variable requires using a SELECT INTO statement. For more information on the SELECT INTO statement refer to Chapter 4 .

The second attribute is the %ROWTYPE. It declares a row with the structure of the specified table. The table must be an existing table or view in the database. After you have declared a variable as a ROWTYPE, you can access the columns in that row by using the dot(.) notation. The syntax to use a %ROWTYPE attribute is:

 name table%ROWTYPE;
   

This function uses a variable which has the same structure as a row in the customer table. First, it copies the row in the customer table which matches the customer number passed into it. Then it displays the corresponding customer name:

 
 CREATE FUNCTION display_cust(integer) RETURNS text AS'
 DECLARE
   -- defines an alias name 
   -- for the customer id number input
   cust_num ALIAS FOR $1;

   -- defines a row type to hold the information
   cust_info customer%ROWTYPE;

 BEGIN
   -- puts information into the newly declared rowtype
   SELECT INTO cust_info * 
     FROM customer 
    WHERE cust_id=cust_num;    

   -- displays the customer lastname
   -- extracted from the rowtype   
   return cust_info.lastname;

 END;
 ' LANGUAGE 'plpgsql';
   

If you pass customer id number 10 into this function, it will return the name Nathan:

   select display_cust(10);

 display_cust
--------------
 Nathan
(1 row)
   

Control Structures

Control structures are ways to regulate the data. Control structures are important because they:

• Are very useful in writing code that gives flexibility to the programmer.

• Can be used to manipulate the PostgreSQL data by using logic to specify what should be done when a user tries to change or modify the data.

• Gives power to the programmer because the programmer can specify when a situation occurs, the function should do this or that.

There are two different types of control structures. The first type is the IF statement. IF statements can give the user control over the action that occurs when a condition exists. The second type is the iterative loop. Iterative loops allow the user to repeatedly perform an action for a true condition.

IF statements

IF statements allow the programmer to specify an action to be taken when a condition occurs. There are three types of IF statements. They are:

	Number of END IFs
	Although the structure of each if statement is different, this rule applies to every type of IF statement: ALL beginning if statements need a correspond END IF.

Iteration

The second type of control structure is through iteration. It controls the execution of your programs. This is done through the commands: LOOP, WHILE, FOR, and EXIT. The syntax for a simple unconditional LOOP statement is:

 LOOP 
     statements
 END LOOP;
   

An unconditional loop statement will continue to loop until it reaches an EXIT. The EXIT command explicitly terminates unconditional loops. The syntax for the EXIT command is:

 EXIT [label] [WHEN expression];
  

The label specifies the level of nesting that should be terminated. Label is the current or an upper level loop block. If a label is not specified, then the innermost loop is terminated and it jumps to the statement after the END LOOP.

For instance, the loop_addtwo() function counts by two and continues to loop until it reaches 20. It begins the count at the number supplied by the user:

   CREATE FUNCTION loop_addtwo(integer) RETURNS integer AS'
   DECLARE
     -- defines a name for the input number
     num1 ALIAS FOR $1;

     -- defines a variable to hold the answer
     answer integer;
 
   BEGIN     
     -- assigns the input number into the answer
     answer = num1;

     -- adds the starting number into the table 
     INSERT INTO count_by_two VALUES (answer);

     LOOP
       -- counts by 2 each time this loop is entered
       answer = answer + 2;
       -- adds each resulting number into the count_by_two table
       INSERT INTO count_by_two VALUES(answer);
       -- exits the loop when the number is higher than 20
       EXIT WHEN answer >= 20;
     -- end of the loop body
     END LOOP;

   -- displays a 0 because this function 
   -- is defined to return an integer
   return 0;

   END;
   ' LANGUAGE 'plpgsql';
   

We used this command to count from 2 to 20:

   SELECT loop_addtwo(2);
   

The count_by_two table now contains these data values:

  number
 --------
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows)
   

This loop is the most basic loop. That is why it needs to use the EXIT command to get out of the loop. If the EXIT command is not supplied, it will never reach the end of the loop.

Another way to control your programs is to use the WHILE loop. The syntax for a WHILE loop is:

  WHILE expression LOOP
     statements
  END LOOP;
   

This function uses the WHILE loop to continue adding the number 2 to a user supplied number. It then inserts each number into the count_by_two table until it reaches the number 20.

 
   CREATE FUNCTION while_addtwo(integer) RETURNS integer AS'
   DECLARE
     -- defines a name for the input number
     num1 ALIAS FOR $1;

     -- defines a variable to hold the answer
     answer integer;

   BEGIN
     -- assigns the input number into the answer
     answer = num1;

     -- adds the starting count number 
     -- into the count_by_two table
     INSERT INTO count_by_two VALUES (answer);

     -- continues to count by 2 if the answer is less than 20
     WHILE answer < 20 LOOP

       -- adds 2 each time this loop is entered and 
       -- inserts the answer into the count_by_two table
       answer = answer + 2;
       INSERT INTO count_by_two VALUES(answer);

     -- end of the loop body
     END LOOP;

   -- displays a 0 because this function 
   -- is defined to return an integer
   return 0;

   END;
   ' LANGUAGE 'plpgsql';   
   

Try calling this function and count from 2. If you do a select * on the count_by_two table, you should see this displayed:

 
   number
  -------- 
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows) 
 

The FOR command iterates over a range of integer values. It will only iterate through one step at a time. The syntax to use the FOR loop is:

  FOR name IN [REVERSE] expression .. expression LOOP
     statements
  END LOOP;
   

The name specified in the FOR loop syntax is automatically created as type integer. The name exists only inside of the FOR loop to be used for incrementing purposes.

The next function adds two to a user supplied number and inserts each answer into the count_by_two table.

  CREATE FUNCTION for_addtwo(integer) RETURNS integer AS'
  DECLARE
    -- defines an alias name for the input
    num1 ALIAS FOR $1;
   
    -- defines a variable to hold the answer
    answer integer;

  BEGIN
    -- assigns the input into the answer
    answer = num1;

    -- inserts the starting count number 
    -- into the count_by_two table
    INSERT INTO count_by_two VALUES (answer);
  
    -- loops through this loop 9 times 
    FOR i IN 1..9 LOOP
      -- adds two to the answer
      answer = answer + 2;
      -- inserts the answer into the count_by_two table
      INSERT INTO count_by_two VALUES (answer);
    -- end of the loop body
    END LOOP;
  
  -- displays 0 because this function 
  -- is defined to return an integer
  return 0;

  END;
  ' LANGUAGE 'plpgsql';
   

When supplied with the number 2, this function should have inserted the same numbers into the count_by_two table as the other two functions previously defined:

    number
   --------
      2
      4
      6
      8
     10
     12
     14
     16
     18
     20
(10 rows)
   

To see the difference between these functions, we will use the number 14 to call all three functions. The simple loop_addtwo () function returns this when supplied with a 14:

   number
  --------
     14
     16
     18
     20
(4 rows)
   

The while_addtwo () function returns a similar answer when supplied with a 14:

 
   number
  --------
     14
     16
     18
     20
(4 rows)
   

Lastly, the for_addtwo () function returns the following when a 14 is supplied:

   number
  --------
     14
     16
     18
     20
     22
     24
     26
     28
     30
     32
(10 rows)
   

The FOR loop will always return a different count when a number other than 2 is supplied. It does not stop counting at number 20, but continues to count until it has counted 10 times. On the other hand, the basic LOOP and WHILE loop will always stop counting when it reaches the number 20, no matter where it started counting from.