Chapter 13. Extending SQL: Functions

Table of Contents
13.1. Query Language (SQL) Functions
13.1.1. Examples
13.1.2. SQL Functions on Base Types
13.1.3. SQL Functions on Composite Types
13.2. Procedural Language Functions
13.3. Internal Functions
13.4. Compiled (C) Language Functions
13.4.1. Base Types in C-Language Functions
13.4.2. Version-0 Calling Conventions for C-Language Functions
13.4.3. Version-1 Calling Conventions for C-Language Functions
13.4.4. Composite Types in C-Language Functions
13.4.5. Writing Code
13.4.6. Compiling and Linking Dynamically-Loaded Functions
13.5. Function Overloading

As it turns out, part of defining a new type is the definition of functions that describe its behavior. Consequently, while it is possible to define a new function without defining a new type, the reverse is not true. We therefore describe how to add new functions to Postgres before describing how to add new types.

Postgres SQL provides three types of functions:

Every kind of function can take a base type, a composite type or some combination as arguments (parameters). In addition, every kind of function can return a base type or a composite type. It's easiest to define SQL functions, so we'll start with those. Examples in this section can also be found in funcs.sql and funcs.c.

13.1. Query Language (SQL) Functions

SQL functions execute an arbitrary list of SQL queries, returning the results of the last query in the list. SQL functions in general return sets. If their returntype is not specified as a setof, then an arbitrary element of the last query's result will be returned.

The body of a SQL function following AS should be a list of queries separated by semicolons and bracketed within single-quote marks. Note that quote marks used in the queries must be escaped, by preceding them with a backslash.

Arguments to the SQL function may be referenced in the queries using a $n syntax: $1 refers to the first argument, $2 to the second, and so on. If an argument is complex, then a dot notation (e.g. "$1.emp") may be used to access attributes of the argument or to invoke functions.

13.1.1. Examples

To illustrate a simple SQL function, consider the following, which might be used to debit a bank account:

CREATE FUNCTION tp1 (int4, float8) 
    RETURNS int4
    AS 'UPDATE bank 
        SET balance = bank.balance - $2
        WHERE bank.acctountno = $1;
        SELECT 1;'
LANGUAGE 'sql';
     
A user could execute this function to debit account 17 by $100.00 as follows:
SELECT tp1( 17,100.0);
     

The following more interesting example takes a single argument of type EMP, and retrieves multiple results:

CREATE FUNCTION hobbies (EMP) RETURNS SETOF hobbies
    AS 'SELECT hobbies.* FROM hobbies
        WHERE $1.name = hobbies.person'
    LANGUAGE 'sql';
     

13.1.2. SQL Functions on Base Types

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

CREATE FUNCTION one() 
    RETURNS int4
    AS 'SELECT 1 as RESULT;' 
    LANGUAGE 'sql';

SELECT one() AS answer;

+-------+
|answer |
+-------+
|1      |
+-------+
     

Notice that we defined a column name for the function's result (with the name RESULT), but this column name is not visible outside the function. 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      |
+-------+
     

13.1.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
    FROM EMP
    WHERE EMP.cubicle ~= point '(2,1)';


+-----+-------+
|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 notations attribute(table) and table.attribute interchangably:

--
-- this is the same as:
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    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 row. We do this by assembling the entire row within the function, attribute by attribute. This is an example of a function that returns a single EMP row:

CREATE FUNCTION new_emp() 
    RETURNS EMP
    AS 'SELECT text ''None'' AS name,
        1000 AS salary,
        25 AS age,
        point ''(2,2)'' AS cubicle'
    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 that defined the composite type.

  • You must typecast the expressions to match the composite type's definition, or you will get errors like this:

    	 ERROR:  function declared to return emp returns varchar instead of text at column 1
    	 
    	

  • When calling a function that returns a row, we cannot retrieve the entire row. We must either project an attribute out of the row or pass the entire row 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;
    NOTICE: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.

CREATE FUNCTION clean_EMP () 
    RETURNS int4
    AS 'DELETE FROM EMP 
        WHERE EMP.salary <= 0;
        SELECT 1 AS ignore_this;'
    LANGUAGE 'sql';

SELECT clean_EMP();

+--+
|x |
+--+
|1 |
+--+