Creating SQL functions

The simplest kind of function to add to PostgreSQL is a pure SQL function, as it requires no external programming knowledge or experience. A SQL function is merely defined as a standard SQL statement with support for inline arguments passed as positional parameters .

A positional parameter is a reference used in a SQL function definition to one of the calling arguments. It is called positional because it is referenced by the order in which the arguments are passed to the function. The syntax of a positional parameter is a dollar sign followed by a number (e.g., $1). The number represents the ordered position in the arguments passed to the function, starting with 1.

Example 7-46 creates a function named isbn_to_title, which returns the title of a book when passed the ISBN number of the book. It accepts a single argument of type text, and returns its result as the same type.

booktown=# 
CREATE FUNCTION isbn_to_title(text) RETURNS text

booktown-# 
                AS 'SELECT title FROM books

booktown'# 
                                 JOIN editions AS e (isbn, id)

booktown'# 
                                 USING (id)

booktown'# 
                                 WHERE isbn = $1'

booktown-# 
                LANGUAGE 'SQL';

CREATE

Notice the $1 in Example 7-46; when the select statement executes, the complete value of the first argument to isbn_to_title replaces this positional parameter. Notice that the positional parameter does not need to be bound by single quotes, as the quotes are part of the argument passed. Each other element of the function definition is either a standard SQL keyword or identifier.

The CREATE message indicates that the function was successfully created. Example 7-47 calls the isbn_to_title function with a single text parameter of 0929605942 . The title, from the books table, returns that ISBN number as per the SQL defined in Example 7-46.

booktown=# 
SELECT isbn_to_title('0929605942');

    isbn_to_title
---------------------
 The Tell-Tale Heart
(1 row)

Once created, any user may access the function, presuming that they have the permission to execute the SQL involved. For example, the isbn_to_title function requires read access to the editions and books tables (see Chapter 10 for more information on user privileges).

Creating C functions

PostgreSQL is written in C and can dynamically load compiled C code for use on the fly, without recompilation of the base software. Only superusers are allowed to use CREATE FUNCTION to link to a C function, as functions can make system-level calls and potentially provide a security hole.

Documenting the entire PostgreSQL API is outside the scope of this book, but for an experienced programmer, some basic C functions can very easily be developed, compiled and linked through loadable shared object code.

The GNU C Compiler, gcc , supports a flag called -shared , which creates a dynamically loadable piece of object code. The most basic syntax to create such a function with gcc is:

$ 
gcc -shared input.c -o output.so

In this syntax, input.c is the name of the file containing the C code to be compiled, and output.so is the shared object file to build.

Example 7-48 is an extremely simple pair of C functions. They define two C functions called is_zero(int) and is_zero_two(int, int). The first function returns true (1) if the passed argument to it is 0; otherwise, it returns false (0). The second function returns true if at least one of the passed arguments is 0.

/* is_zero.c
 * A pair of simple zero-checking functions.
 */

int is_zero(int);
int is_zero_two(int, int);

int is_zero(int incoming) {
  /* Return true only if the incoming value is 0. */
  if (incoming == 0) return 1;
  else return 0;
}

int is_zero_two(int left, int right) {
  /* Return true only if either of the values are 0. */
  if (left == 0 || right == 0) return 1;
  else return 0;
}

Example 7-49 compiles the file is_zero.c , with the -shared flag, and outputs the shared object code to a file called is_zero.so . The location of that file is then passed as the definition of the function to the CREATE FUNCTION command, and the function type is defined as C .

[jworsley@cmd ~]$ 
gcc -shared is_zero.c -o is_zero.so

[jworsley@cmd ~]$ 
psql -U manager booktown

Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

booktown=# 
CREATE FUNCTION is_zero(int4) RETURNS Boolean 

booktown-# 
                AS '/home/jworsley/is_zero.so' LANGUAGE 'C';

CREATE

The CREATE FUNCTION command in Example 7-49 creates a function named is_zero(), which accepts a single argument of type int4 and returns a value of type boolean. This function references the C function is_zero(int) implemented in the object code located at /home/jworsley/is_zero.so (since C has no Boolean type, PostgreSQL must transform the integer value returned by the function to a Boolean value). In this case, 0 is translated to false, and 1 is translated to true.

By default, PostgreSQL looks for a function in the shared object code with the same name as the function being created within PostgreSQL. This works well for the is_zero(integer) function, as its names matches the compiled symbol name of the is_zero(int) function within the file is_zero.so . In order to avoid a C name-collision with is_zero(int), the second function in the shared object is defined as is_zero_two(int, int). To load this function into PostgreSQL with the same name (as an overloaded function, with two arguments instead of one), pass the literal C function name (also called the link symbol) as a second string constant following the location of the shared object filename.

This name should not contain parentheses or arguments, and should be separated from the filename definition by a comma, as in this syntax:

CREATE FUNCTION 
name
 ( [ 
argumenttype
 [, ...] ] )
                RETURNS 
returntype

                AS '
definition
', '
link_symbol
'
                LANGUAGE 'C'
                [ WITH ( 
attribute
 [, ...] ) ]

Example 7-50 loads the same shared object code, but specifies the function symbol name as is_zero_two so that it knows which function to use for this overloaded function.

booktown=# 
CREATE FUNCTION is_zero(int4, int4) RETURNS Boolean

booktown-# 
                AS '/home/jworsley/is_zero.so', 'is_zero_two'

booktown-# 
                LANGUAGE 'C';

CREATE

Like a SQL function, any user may call the C function once it has been created. As C functions can make direct modifications to the filesystem (where permissions allow) and affect other system level events, care must be taken in designing functions free from potential misuse. Example 7-51 makes several calls to the is_zero function defined in Example 7-49, and to its overloaded function, created in Example 7-50.

booktown=# 
SELECT is_zero(0) AS zero, is_zero(1) AS one,

booktown-# 
       is_zero(6, 0) AS one_zero, is_zero(11,12) AS neither;

 zero | one | one_zero | neither
------+-----+----------+---------
 t    | f   | t        | f
(1 row)

Destroying functions

Functions may be destroyed either by their owner or by a superuser with the DROP FUNCTION SQL command. Here is the syntax for DROP FUNCTION:

  DELETE FUNCTION 
name
 ( [ 
argumenttype
 [, ...] ] );

For example, Example 7-52 drops the isbn_to_title(text) function. Note that the argument types are required to be specified, even though the function itself is not overloaded.

booktown=# 
DROP FUNCTION isbn_to_title(text);

DROP

The DROP server message indicates that the function was successfully dropped. Like most DROP SQL commands, this action is permanent, so be sure that you wish to drop your function before you execute this command.