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:

Example 9-16. PL/pgSQL Aliases

 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)
   

Note 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.