9.1 Writing SQL Functions

Writing functions with the help of SQL is just as easy as writing SQL statements. The idea behind SQL functions is to have an easy way to add new features to a database. In addition, it is not necessary to use embedded languages such as PL/pgSQL or PL/Perl for implementing minor extensions. This is an important point because it increases the portability and flexibility of your database significantly.

9.1.1 An Example

Let's start by writing a simple function based on pure SQL code. For adding new functions to the database, you must use CREATE FUNCTION. The syntax of CREATE FUNCTION can easily be retrieved from the database by using the \h command:

 phpbook=# \h CREATE FUNCTION Command:     CREATE FUNCTION Description: define a new function Syntax: CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )     RETURNS rettype     AS 'definition'     LANGUAGE langname     [ WITH ( attribute [, ...] ) ] CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )     RETURNS rettype     AS 'obj_file', 'link_symbol'     LANGUAGE langname     [ WITH ( attribute [, ...] ) ] 

As you can see, the command is very powerful and allows you to add functions easily. Let's take a look at a practical example:

 CREATE FUNCTION pythagoras (numeric, numeric) RETURNS numeric AS '         SELECT sqrt($1 * $1 + $2 * $2) ' LANGUAGE 'sql'; 

The function is called pythagoras and accepts two numeric values. It can be used to compute the length of the longest component in a triangle containing a 90-degree angle. In Figure 9.1, it is the line labeled as c.

Figure 9.1. A triangle.

The data type returned by the function is numeric and the result will be of high precision. To insert the function into a database, you can either use PostgreSQL's interactive shell or use a file containing the code, as shown in the next example:

 bash-2.04$ psql phpbook < function.sql CREATE 

The file called function.sql contains the code of the function and is sent to psql. If no error is returned, the function has been added to the database successfully. Keep in mind that this does not mean that the function is correct it just means that a function with the desired name and desired number of parameters has been added.

To test the function, you can execute it using a simple SELECT statement:

 phpbook=# SELECT pythagoras(3, 4);   pythagoras --------------  5.0000000000 (1 row) 

The result is exactly five, which is the correct mathematical result. If you try to add the same function to the server again, it will fail because there is already a function with the same name and number of parameters in the database:

 bash-2.04$ psql phpbook < function.sql ERROR:  function pythagoras already exists with same argument types ERROR:  function pythagoras already exists with same argument types 

If you want the old function that is already in the database to be replaced, use CREATE OR REPLACE instead of CREATE. This will drop the function implicitly and add the new version of the function to the database:

 CREATE OR REPLACE FUNCTION pythagoras (numeric, numeric) RETURNS numeric AS '         SELECT sqrt($1 * $1 + $2 * $2) ' LANGUAGE 'sql'; 

When the function is added to the database, no errors will be displayed.

9.1.2 Removing Functions

If you want to remove a function from the database, you must use DROP FUNCTION. Let's take a look at the syntax specification of DROP FUNCTION:

 phpbook=# \h DROP FUNCTION Command:     DROP FUNCTION Description: remove a user-defined function Syntax: DROP FUNCTION name ( [ type [, ...] ] ) 

To remove the function you have just dealt with, use the following command:

 phpbook=# DROP FUNCTION pythagoras(numeric, numeric); DROP 

9.1.3 Caching

When you're working with complex functions, caching is an important thing. The longer a function is and the more time it takes to compute the result, the more important it will be to cache the result of it in order to speed up your applications.

To perform caching, an attribute must be added to the function. This can be done with the help of WITH (iscachable):

 CREATE OR REPLACE FUNCTION pythagoras (numeric, numeric) RETURNS numeric AS '         SELECT sqrt($1 * $1 + $2 * $2) ' LANGUAGE 'sql' WITH (iscachable); 

After reinserting the function into the database, we execute the same SQL statement twice:

 bash-2.04$ time psql -d phpbook -c "SELECT pythagoras(30000, 400000000000)"        pythagoras -------------------------  400000000000.0011250000 (1 row) real    0m0.146s user    0m0.030s sys     0m0.000s bash-2.04$ time psql -d phpbook -c "SELECT pythagoras(30000, 400000000000)"        pythagoras -------------------------  400000000000.0011250000 (1 row) real    0m0.068s user    0m0.020s sys     0m0.000s 

As you can see, the second time the function is executed, it is significantly faster because of caching effects. The amount of time saved by caching need not always be as huge as shown in the preceding example, but the longer the cached function is, the higher the gain in speed will be.

Take a look at the next example:

 CREATE OR REPLACE FUNCTION givetime () RETURNS timestamp AS '         SELECT now() ' LANGUAGE 'sql' WITH (iscachable); 

This time you have written a function returning the result of the now() function. Although the function is defined as iscachable, the result will not always be the same:

 phpbook=# SELECT givetime();            givetime -------------------------------  2001-11-10 14:17:32.350287+01 (1 row) phpbook=# SELECT givetime();            givetime ------------------------------  2001-11-10 14:17:38.25615+01 (1 row) 

In this example PostgreSQL makes sure that the result of the function is computed again.

9.1.4 Handling NULL Values in SQL Functions

When NULL values are passed to a function, it might not be useful to compute a result because it is NULL anyway. In this case, WITH (isstrict) can be used to tell PostgreSQL that if a NULL value is passed to the function it has not been executed, but NULL must be returned:

 CREATE OR REPLACE FUNCTION pythagoras (numeric, numeric) RETURNS numeric AS '         SELECT sqrt($1 * $1 + $2 * $2) ' LANGUAGE 'sql' WITH (isstrict); 

Let's run the function:

 bash-2.04$ time psql -d phpbook -c "SELECT pythagoras(3, NULL)"  pythagoras ------------ (1 row) real    0m0.062s user    0m0.030s sys     0m0.010s 

NULL is being returned without executing the function. When you're dealing with huge and complex examples, this can lead to faster execution.

9.1.5 Function Overloading

Because PostgreSQL has strong object-oriented capabilities, it is possible to work with function overloading. Function overloading means that functions with the same name can exist and each of these functions accepts a different list of parameters.

Let's take a look at an example. The goal is to write two functions for generating the arithmetic mean of two or three values. Therefore two functions will be implemented. The first function accepts just one parameter, and the second function is capable of working with three parameters:

 CREATE OR REPLACE FUNCTION average (numeric, numeric)         RETURNS numeric AS '         SELECT ($1 + $2) / 2; ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION average (numeric, numeric, numeric)         RETURNS numeric AS '         SELECT ($1 + $2 + $3) / 3; ' LANGUAGE 'sql'; 

Although both functions have the same name, they will be treated as independent functions because the list of parameters accepted by the functions differs. Let's execute both functions to see that the correct result is generated:

 phpbook=# SELECT average(1, 3);    average --------------  2.0000000000 (1 row) phpbook=# SELECT average(1, 3, 4);    average --------------  2.6666666667 (1 row) 

In the first example, the function accepting two parameters is called. In the second example, the function accepting three parameters will be executed.

9.1.6 CASE Statements within SQL

Sometimes it is necessary to perform simple, implicit decisions within a SQL statement. Therefore PostgreSQL provides CASE statements, which you can use to build decision functions.

Take a look at a simple example. First a table is created:

 phpbook=# CREATE TABLE test (name text); CREATE 

After that some data can be inserted into the database by using the COPY command:

 phpbook=# COPY test FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 >> 3 >> 1 >> \. 

Let's see what is in the table:

 phpbook=# SELECT * FROM test;  name ------  1  3  1 (3 rows) 

In the next step you can build a function that is similar to Oracle's decode function. The purpose of this function is to perform simple substitutions:

 CREATE OR REPLACE FUNCTION mydecode (text, text, text)         RETURNS text AS '         SELECT CASE $1 WHEN $2 THEN $3 ELSE $1 END; ' LANGUAGE 'sql'; 

The first parameter passed to the function is the value that must be checked. If it is equal to the second parameter, it is substituted for the third parameter. In this example the code of the function is stored in if.sql and can easily be inserted into the database:

 bash-2.04$ psql phpbook < if.sql CREATE 

After inserting the function, it can easily be tested by writing a simple SQL statement:

 phpbook=# SELECT mydecode(name, 1, 999) FROM test;  mydecode ----------  999  3  999 (3 rows) 

As you can see, every time 1 is found in the table it is substituted for 999, which is the third parameter passed to the function.

If additional conditions have to be checked, an additional function must be written. In the next example you can see how two values can be checked.

 CREATE OR REPLACE FUNCTION mydecode (text, text, text, text, text)         RETURNS text AS '         SELECT CASE $1 WHEN $2 THEN $3                 WHEN $4 THEN $5 ELSE $1 END; ' LANGUAGE 'sql'; 

This function can be used to check two conditions. If the value passed to the function as the fourth parameter is found, it is substituted for the fifth parameter:

 phpbook=# SELECT mydecode(name, 1, 999, 3, 23) FROM test;  mydecode ----------  999  23  999 (3 rows) 

As you can see, 3 has been substituted for 23. However, the problem can also be solved differently by calling mydecode recursively:

 phpbook=# SELECT mydecode(mydecode(name, 1, 999), 3, 23) FROM test;  mydecode ----------  999  23  999 (3 rows) 

mydecode is called two times. The result of the first function is returned to and used by the second function.

With the help of recursive function calls, it is possible to build complex applications.