The structure of a PL/pgSQL function
It doesn't take much to get a PL/pgSQL function working. Here's a basic example:
CREATE FUNCTION mid(varchar, integer, integer) RETURNS varchar AS $$ BEGIN RETURN substring($1,$2,$3); END; $$ LANGUAGE plpgsql;
The preceding function shows the basic elements of a PL/pgSQL function. It creates an alias for the substring built-in function called mid. This is a handy alias to have around for developers that come from Microsoft SQL Server or MySQL and are wondering what happened to the mid function. It also illustrates the most basic parameter-passing strategy: parameters are not named and are accessed in the function by their relative location from left to right. The $$ character in this example represents the start and end of the code block. This character sequence can be arbitrary and you can use something else of your choice, but this book uses $$ in all the examples.
The basic elements of a PL/pgSQL function are name, parameters, return type, body, and language. It can be argued that parameters are not mandatory for a function and neither is the return value. This might be useful for a procedure that operates on data without providing a response, but it will be prudent to return the value TRUE to indicate that the procedure succeeded.
Accessing function arguments
Function arguments can also be passed and accessed by name, instead of just by the ordinal order. By accessing the parameters by name, it makes the resulting function code a little more readable. The following is an example of a function that uses named parameters:
CREATE FUNCTION mid(keyfield varchar, starting_point integer) RETURNS varchar AS $$ BEGIN RETURN substring(keyfield,starting_point); END $$ LANGUAGE plpgsql;
The preceding function also demonstrates the overloading of the mid function. Overloading is another feature of PostgreSQL functions, which allows multiple procedures to use the same name, but a different number or types of parameters. In this case, we first declared the mid function with three parameters. However, in this example, overloading is used to implement an alternative form of the mid function, where there are only two parameters. When the third parameter is omitted, the result will be a string starting from starting_point and continuing to the end of the input string, as shown here:
SELECT mid('Kirk L. Roybal',9);
The preceding line of code yields the following result:
mid -------- Roybal (1 row)
In order to access the function parameters by name, PostgreSQL makes a few educated guesses depending on the statement. Consider, for a moment, the following function:
CREATE OR REPLACE FUNCTION ambiguous(parameter varchar) RETURNS integer AS $$
DECLARE retval integer;
BEGIN
INSERT INTO parameter (parameter) VALUES (parameter) RETURNING id INTO retval;
RETURN retval;
END;
$$
LANGUAGE plpgsql;
SELECT ambiguous ('parameter');
This is an example of positively atrocious programming since the argument, table, and its column are all called parameter. This should never occur outside an example of how not to write functions. However, PostgreSQL is intelligent enough to correctly deduce that the contents of the function parameter are only legal in the VALUES list. All other occurrences of parameter are actually physical PostgreSQL entities.
We also introduced an optional section to the function. We declare a variable before the BEGIN statement. Variables that appear in this section are valid during the execution of the function.
Also note, the RETURNING id INTO retval statement in this function. This feature allows the developer to specify the identity field of the record and returns the value of this field after the record has been inserted. Our function then returns this value to the caller as an indicator that the function succeeded, and as a way to find the record that has been inserted. This is a good way to return values inserted by default, such as the serial sequence numbers. You can use any expression with table column names, and the syntax will be similar to the column list in a SELECT statement.