Writing Functions

Writing user -defined functions is one of the core features of a sophisticated relational database system. PostgreSQL makes writing, inserting, and deleting a function very easy, as you will learn in this section.

Inserting PL/pgSQL Functions into a Database

Functions can directly be written in the psql shell. Because functions are usually many lines long, you should use your favorite text editor ” vi, for example. The text file containing the source code can then easily be inserted into the database using a command like the following:

  psql yourdb < yourcode.sql  

If the command quits successfully, the function can be used in the database. The output of this command varies, depending on the SQL code you want to insert into the database.

Note

Don't forget to enable PL/pgSQL for your database.

A Simple Example

Let's get to some practical stuff. Here is a simple function that calculates the length of a string passed to it:

  CREATE FUNCTION calclength (text) RETURNS int4 AS   '   DECLARE   intext ALIAS FOR ;   result int4;   BEGIN   result := (SELECT LENGTH(intext));   RETURN result;   END;   ' LANGUAGE 'plpgsql';  

The code starts with the CREATE FUNCTION and a name of the function is given. The datatypes of the parameters passed to the function are given in brackets. In this example, a single parameter of type text is passed to the function, but it could be any other datatype supported by SQL. All datatypes that can be used in plain SQL code can also be used by PL/pgSQL The next step is to specify the datatype of the return value ”in this case, the function should return a 4-byte integer, which is the length of the string.

The body of the function has to be passed to the database as one string (Yyou can see that the first and the last line start with a single quote). After the string, the programming language used to execute the string is defined by using the LANGUAGE command. In this case, we use plpgsql (PL/pgSQL) for executing the code. PostgreSQL not only supports PL/pgSQL; you can also use PL/Perl or C code to write functions. It is important to know that the definition of the programming language is not part of the PL/pgSQL code. The LANGUAGE command is used only to tell PostreSQL what kind of source code is passed to the database engine.

Let's take a closer look at the DECLARE section. First, the variable intext is being declared as an alias for $1 . $1 is the first parameter passed to the function (Bourne Shell uses the same notation). A second parameter would be called $2 , and so on. In the next line, a 4-byte integer variable has to be declared. This variable will be used as the return value of the function. You can see that the variable result is being assigned the same datatype as the return value of calclength(text) ; compare RETURNS int4

 AS. 

The BEGIN / END section contains the actual code of the function. The length of intext is assigned to result .

Note

You have to use := instead of = for assigning a value to a variable.

Within a BEGIN / END section, SQL code can be used as it is within the psql shell. After inserting the function into the database, the function can be called the following way:

  SELECT calclength('abc');  

The result of the function call is 3:

 calclength ------------           3 (1 row) 

To remove the function from the database, you can use the DROP FUNCTION command:

 yourdb=# DROP FUNCTION calclength(text); 

Using Typecast Operations

Typecasting is very important for database programming. Typecasting means that a variable with a certain datatype is converted into a different type. Let's see how typecasting can be used in combination with PL/pgSQL functions.

PostgreSQL converts the variables to the right datatype implicitly. When I wrote that text there was no PostgreSQL 7.1 available yet. I have changed the entire section and added some additonal information.

We have already seen in the previous example that PostgreSQL assumes that the string abc is a text variable. If we do not call calclength with a string value, PostgreSQL will try to convert the value to a suitable datatype:

 yourdb=#  SELECT calclength(3);  calclength ------------           1 (1 row) 

The integer value 3 can be cast to integer, so the function can be executed successfully.

Casting a value to the appropriate datatype is not always possible:

 yourdb=#  SELECT calclength('2,0'::point);  ERROR:  Function 'calclength(point)' does not exist         Unable to identify a function that satisfies the given argument types 

You can see in the listing that PostgreSQL cannot find the right function, because point cannot be converted to text . You can see an explicit cast in the next example:

 yourdb=#  SELECT calclength('2,0'::point::text);  ERROR:  Cannot cast type 'point' to 'text' 

point cannot be cast to text , so the function call fails.

Casting cannot only be performed by using the :: operator. PostgreSQL supports a variety of functions to perform cast operations.

text('abc') casts 'abc' to text. Let's try another cast:

  SELECT int4('3');  

This returns 3 .

As you have seen before not every variable can be cast to every datatype:

  SELECT int4('a');  

This returns the following error:

 ERROR: pg_atoi: error in "a": can't parse "a" 

This error message means that PostgreSQL cannot perform an ASCII-to-integer conversion when using the string "a" ( atoi = ASCII to integer).

Simple Flow of Control Structures

Programs and functions are not usually executed from the beginning to the end without interruption. In most cases, functions contain control structures that are used to influence the behavior of a program depending on the data that is processed . Like all programming languages PL/pgSQL supports a pool of control structures:

Using IF / ELSE

Now we want to write a function that calculates the length of two strings and returns the length of the longer one:

  CREATE FUNCTION calclonger(text,text) RETURNS int4 AS   '   DECLARE   in_one  ALIAS FOR ;   in_two  ALIAS FOR ;   len_one int4;   len_two int4;   result  int4;   BEGIN   len_one := (SELECT LENGTH(in_one));   len_two := (SELECT LENGTH(in_two));   IF      len_one > len_two THEN   RETURN len_one;   ELSE   RETURN len_two;   END IF;   END;   ' LANGUAGE 'plpgsql';  

PL/pgSQL supports, like most other languages, IF / ELSE statements. Our example is indeed very simple. We calculate the length of both strings and return the higher value. Because PL/pgSQL is block-oriented, we do not need brackets inside the IF .

If more than one condition has to be fullfilled, IF can be used with AND or OR statements:

  IF      len_one > 20 AND len_one < 40 THEN   RETURN len_one;   ELSE   RETURN len_two;   END IF;  

In this case, len_one is returned when len_one is higher than 20 but lower than 40 . We would recommend using brackets to make the code clearer:

 IF      (len_one > 20) AND (len_one < 40) THEN ... 

Using LOOP / EXIT , WHILE, and FOR Loops

Loops can be used to execute a piece of source code several times. Now we want to write a function that counts the frequency of a certain character in a string:

  CREATE FUNCTION countc (text, text) RETURNS int4 AS '   DECLARE   intext ALIAS FOR ;   inchar ALIAS FOR ;   len     int4;   result  int4;   i       int4;   tmp     char;   BEGIN   len     := length(intext);   i       := 1;   result  := 0;   WHILE   i<= len LOOP   tmp := substr(intext, i, 1);   IF      tmp = inchar THEN   result := result + 1;   END IF;   i:= i+1;   END LOOP;   RETURN result;   END;   ' LANGUAGE 'plpgsql';  

The first parameter passed to the function is the string that has to be scanned by the function. The second parameter contains the character the function is looking for. First, the length of the first parameter is assigned to the variable len , and then i and result are initialized . Then the string is scanned from the first character to the last, and for every character scanned, the code within the WHILE / END LOOP statements is executed. If the scanned character matches the second parameter, which has been passed to the function, the value of result is incremented by 1 . Finally, the result is returned by the function.

PL/pgSQL also supports FOR loops:

  CREATE FUNCTION countc(text, text, int4, int4) RETURNS int4 AS '   DECLARE   intext          ALIAS FOR ;   inchar          ALIAS FOR ;   startspos       ALIAS FOR ;   eendpos         ALIAS FOR ;   tmp             text;   i               int4;   len             int4;   result          int4;   BEGIN   result = 0;   len := LENGTH(intext);   FOR i IN startpos..endpos LOOP   tmp := substr(intext, i, 1);   IF     tmp = inchar THEN   result := result + 1;   END IF;   END LOOP;   RETURN result;   END;   ' LANGUAGE 'plpgsql';  

The syntax of FOR loops in PL/pgSQL is simple. This example can be used to search a character in a string from position number startpos to position number endpos . The FOR loop assigns a value to i whenever the loop is processed. startpos .. endpos means that processing is started at startpos , and i is incremented by 1 until endpos is reached.

The function can also be implemented differently. This time we want to write a function without using a FOR loop. With the help of LOOP / EXIT , this can be done easily:

  CREATE FUNCTION countc(text, text, int4, int4) RETURNS int4 AS '   DECLARE   intext      ALIAS FOR ;   inchar      ALIAS FOR ;   startpos    ALIAS FOR ;   endpos     ALIAS FOR ;   i          int4;   tmp        text;   len        int4;   result     int4;   BEGIN   result = 0;   i := startpos;   len := LENGTH(intext);   LOOP   IF      i <= endpos AND i <= len THEN   tmp := substr(intext, i, 1);   IF     tmp = inchar THEN   result := result + 1;   END IF;   i := i + 1;   ELSE   EXIT;   END IF;   END LOOP;   RETURN result;   END;   ' LANGUAGE 'plpgsql';  

FOR loops are not used here, so the function is more complex than its counterpart , which you saw before. LOOP / EXIT must perform a check, which tells the interpreter whether to leave the block. Otherwise, the loop won't stop.