Argument Variables

PL/pgSQL functions can accept argument variables of different types. Function arguments allow you to pass information from the user into the function that the function may require. Arguments greatly extend the possible uses of PL/pgSQL functions. User input generally provides a function with the data it will either operate on or use for operation. Users pass arguments to functions when the function is called by including them within parentheses, separated by commas.

Arguments must follow the argument list defined when the function is first created. Example 11-21 shows a pair of example function calls from psql .

booktown=# 
SELECT get_author('John');

  get_author
--------------
 John Worsley
(1 row)

booktown=# 
SELECT get_author(1111);

  get_author
--------------
 Ariel Denham
(1 row)

Each function argument that is received by a function is incrementally assigned to an identifier that begins with the dollar sign ($) and is labeled with the argument number. The identifier $1 is used for the first argument, $2 is used for the second argument, and so forth. The maximum number of function arguments that can be processed is sixteen, so the argument identifiers can range from $1 to $16. Example 11-22 shows a function that doubles an integer argument variable that is passed to it.

CREATE FUNCTION double_price (float) RETURNS float AS '
  DECLARE
  BEGIN
     
     -- Return the argument variable multiplied by two.
    return $1 * 2;
  
  END;
' LANGUAGE 'plpgsql';

Referencing arguments with the dollar sign and the argument's order number can become confusing in functions that accept a large number of arguments. To help in functions where the ability to better distinguish argument variables from one another is needed (or just when you wish to use a more meaningful name for an argument variable), PL/pgSQL allows you to create variable aliases .

Aliases are created with the ALIAS keyword and give you the ability to designate an alternate identifier to use when referencing argument variables. All aliases must be declared in the declaration section of a block before they can be used (just like normal variables). Example 11-23 shows the syntax of the ALIAS keyword.

CREATE FUNCTION 
function_identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
identifier
 ALIAS FOR $1;
    
identifier
 ALIAS FOR $2;
  BEGIN
    [...]
  END;
' LANGUAGE 'plpgsql';

Example 11-24 creates a simple function to demonstrate the use of aliases in a PL/pgSQL function. The triple_ price() function accepts a floating point number as the price and returns that number multiplied by three.

CREATE FUNCTION triple_price (float) RETURNS float AS '
  DECLARE
     
     -- Declare input_price as an alias for the argument variable
     -- normally referenced with the $1 identifier.
    input_price ALIAS FOR $1;
  
  BEGIN
     
     -- Return the input price multiplied by three.
    
    RETURN input_price * 3;
  
  END;
 ' LANGUAGE 'plpgsql';

Now, if we use the triple_ price function within a SQL SELECT statement in a client such as psql , we receive the results shown in Example 11-25.

booktown=# 
SELECT double_price(12.50);

 double_price
--------------
           25
(1 row)