Writing PostgreSQL Functions with PL/pgSQLby David Wheeler
One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions, however, is PL/pgSQL (don't ask me to pronounce it), because it comes with PostgreSQL and is easy to set up.
PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions, and the like. Because it natively supports all of PostgreSQL's SQL syntax, you can consider it a superset of PostgreSQL SQL. It also respects all data types and their associated functions and operators, and is completely safe for use inside of the server. Get all of the details in the PL/pgSQL documentation.
To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the
template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the
SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';
If the result row has the value
true, PL/pgSQL is already installed in your database. If not, quit
psql and execute the command:
$ createlang plpgsql database_name
To add a language, you must have superuser access to the database. If
you've just installed PostgreSQL, then you can likely use the default
postgres user by passing
-U postgres to
createlang. From this point, you should be able to follow along
by pasting the sample functions into
A First Function
To write your first PL/pgSQL function, start with something simple: a function to return the Fibonacci number for a position in the Fibonacci sequence. I know, I know; everyone uses a Fibonacci calculator to demonstrate code. Why can't I be original? Because a couple iterations of such a function will show off some of the more useful features of PL/pgSQL. It's purely pedagogical. A simple implementation is:
1 CREATE OR REPLACE FUNCTION fib ( 2 fib_for integer 3 ) RETURNS integer AS $$ 4 BEGIN 5 IF fib_for < 2 THEN 6 RETURN fib_for; 7 END IF; 8 RETURN fib(fib_for - 2) + fib(fib_for - 1); 9 END; 10 $$ LANGUAGE plpgsql;
Using the function is easy:
try=% select fib(8); fib ----- 21 (1 row)
The first line uses PostgreSQL's
CREATE OR REPLACE FUNCTION statement to create the function. The name of the function is
CREATE OR REPLACE FUNCTION statement is more useful in practice than the simple
CREATE FUNCTION statement, because it will drop an existing function with the same name and argument signature before creating the new one. This is very convenient while you're developing and testing a new function.
The second line declares the integer variable
fib_for as the sole argument to the function, and thus constitutes its entire argument signature. The argument signature must come after the name of the function, inside parentheses. In this respect, it's not much different than function or method declarations in most programming languages. Arguments can be of any type supported by PostgreSQL, including user-created types and domains, as well as composite data types such as table row types. This article's examples will use only simple data types, but see the PL/pgSQL Declarations documentation for details.
Note that named arguments were added to PL/pgSQL in PostgreSQL 8.0. In earlier versions of PostgreSQL, you must either use the default, numbered variable names for the arguments, or declare aliases in a
CREATE OR REPLACE FUNCTION fib ( integer ) RETURNS integer AS $$ DECLARE fib_for ALIAS FOR $1; BEGIN -- ...
Unless you have an older version of PostgreSQL, use named arguments. They're more convenient.
The third line closes the argument signature and specifies the function return value (
integer). As with arguments, the return value of a function can be any PostgreSQL data type, including a composite type or even a cursor. The end of line three has the odd string
$$. This is PostgreSQL dollar-quoting. When used in place of the usual single-quotation mark quoting (
'), you don't have to escape single quotation marks within the body of the function. This makes them much easier to read.
BEGIN statement marks the start of the function body, while lines 5-8 are the function body, implenting the standard recursive algorithm for determining a Fibonacci number. Lines 5-7 use the PL/pgSQL
IF-THEN conditional statement to return the sequence number itself if it is less than two. As with all blocks in PL/pgSQL, the
IF-THEN conditional ends with a final
END statement. Conditional expressions in PL/pgSQL can be any SQL expression that you might use in the
WHERE clause of a typical
SELECT statement. The nice thing here, however, is that you can use a variable (in this case,
fib_for) in the expression.
Line five demonstrates the ability of PL/pgSQL to not only execute other PL/pgSQL functions, but to do so recursively. In this case, the
fib() function calls itself twice in order to properly determine and return the Fibonacci number. Note that you can use the PL/pgSQL
RETURN keyword anywhere in a PL/pgSQL function to terminate the execution of the function and return a value.
END statement signals the end of the function body, while line ten closes the dollar quoting and identifies the function implementation language.
A Note on Statement Termination
At first glance, the placement of semicolons to terminate statements in the example function might appear to be somewhat ad hoc. I assure you that it is not. In PL/pgSQL, all blocks must terminate in a semicolon, as must all statements within that block. The expression that initiates the block, however, such as
BEGIN on line 4 or
IF fib_for < 2 THEN on line five, does not end with semicolons. Line six, as a complete statement within the
IF ... THEN block, ends with a semicolon, as does the statement on line eight.
Perhaps the simplest way to remember this rule is to think of statements as requiring semicolons, and block initiation expressions as not being complete statements. That is, blocks only become complete statements when they