This page in other versions: Unsupported versions: 6.3
Prev Chapter 31. Extending SQL: Functions Next

Query Language (SQL) Functions

SQL Functions on Base Types

The simplest possible SQL function has no arguments and simply returns a base type, such as int4:

     AS 'SELECT 1 as RESULT' LANGUAGE 'sql';

    SELECT one() AS answer;

         |answer |
         |1      |

Notice that we defined a target list for the function (with the name RESULT), but the target list of the query that invoked the function overrode the function's target list. Hence, the result is labelled answer instead of one.

It's almost as easy to define SQL functions that take base types as arguments. In the example below, notice how we refer to the arguments within the function as $1 and $2.

    CREATE FUNCTION add_em(int4, int4) RETURNS int4
     AS 'SELECT $1 + $2;' LANGUAGE 'sql';

    SELECT add_em(1, 2) AS answer;

         |answer |
         |3      |

SQL Functions on Composite Types

When specifying functions with arguments of composite types (such as EMP), we must not only specify which argument we want (as we did above with $1 and $2) but also the attributes of that argument. For example, take the function double_salary that computes what your salary would be if it were doubled.

    CREATE FUNCTION double_salary(EMP) RETURNS int4
     AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql';

    SELECT name, double_salary(EMP) AS dream
     WHERE EMP.dept = 'toy';

         |name | dream |
         |Sam  | 2400  |

Notice the use of the syntax $1.salary. Before launching into the subject of functions that return composite types, we must first introduce the function notation for projecting attributes. The simple way to explain this is that we can usually use the notation attribute(class) and class.attribute interchangably.

    -- this is the same as:
    --  SELECT AS youngster FROM EMP WHERE EMP.age < 30
    SELECT name(EMP) AS youngster
     WHERE age(EMP) < 30;

         |youngster |
         |Sam       |

As we shall see, however, this is not always the case. This function notation is important when we want to use a function that returns a single instance. We do this by assembling the entire instance within the function, attribute by attribute. This is an example of a function that returns a single EMP instance:

     AS 'SELECT \'None\'::text AS name,
      1000 AS salary,
      25 AS age,
      \'none\'::char16 AS dept;'
      LANGUAGE 'sql';

In this case we have specified each of the attributes with a constant value, but any computation or expression could have been substituted for these constants. Defining a function like this can be tricky. Some of the more important caveats are as follows:

  • The target list order must be exactly the same as that in which the attributes appear in the CREATE TABLE statement (or when you execute a .* query).

  • You must typecast the expressions (using ::) very carefully or you will see the following error:

                WARN::function declared to return type EMP does not retrieve (EMP.*)
  • When calling a function that returns an instance, we cannot retrieve the entire instance. We must either project an attribute out of the instance or pass the entire instance into another function.

        SELECT name(new_emp()) AS nobody;
                |nobody |
                |None   |
  • The reason why, in general, we must use the function syntax for projecting attributes of function return values is that the parser just doesn't understand the other (dot) syntax for projection when combined with function calls.

                SELECT new_emp().name AS nobody;
                WARN:parser: syntax error at or near "."

Any collection of commands in the SQL query language can be packaged together and defined as a function. The commands can include updates (i.e., insert, update and delete) as well as select queries. However, the final command must be a select that returns whatever is specified as the function's returntype.

     AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
    SELECT 1 AS ignore_this'
     LANGUAGE 'sql';

    SELECT clean_EMP();

         |x |
         |1 |

Prev Home Next
Extending SQL: Functions Up Programming Language Functions

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group