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
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.
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
As a recursive function, the
fib() example is none too fast. Of course it short-circuits when the argument is less than two, but otherwise its recursion can be quite slow. Why? Because each time you call it, it must calculate and return many of the same numbers. On my PowerBook, for example, it takes nearly 3.8 seconds to find the 26th Fibonacci number:
try=% explain analyze select fib(26); QUERY PLAN ------------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=3772.062..3772.063 rows=1 loops=1) Total runtime: 3772.315 ms (2 rows)
Why does it take so long? Like any recursive Fibonacci function, it has to make 392,835 recursive calls to itself to calculate the 26th Fibonacci number. Those recursive calls add up! Because the function always returns the same values for the same arguments, it would really improve the performance of the function to memoize it. Memoization caches the results of a function call for a given set of arguments so that when the function is called again with the same arguments, it can simply return the value from the cache without recalculating it--in this case, minimizing the need for recursion.
PL/pgSQL itself has no ability to store data outside of a function, but this is database programming--take advantage of it! The trick is to create a table to function as the cache, then access it from the function. The new function,
fib_cached(), does exactly that:
1 CREATE TABLE fib_cache ( 2 num integer PRIMARY KEY, 3 fib integer NOT NULL 4 ); 5 6 CREATE OR REPLACE FUNCTION fib_cached( 7 fib_for integer 8 ) RETURNS integer AS $$ 9 DECLARE 10 ret integer; 11 BEGIN 12 if fib_for < 2 THEN 13 RETURN fib_for; 14 END IF; 15 16 SELECT INTO ret fib 17 FROM fib_cache 18 WHERE num = fib_for; 19 20 IF ret IS NULL THEN 21 ret := fib_cached(fib_for - 2) + fib_cached(fib_for - 1); 22 INSERT INTO fib_cache (num, fib) 23 VALUES (fib_for, ret); 24 END IF; 25 RETURN ret; 26 27 END; 28 $$ LANGUAGE plpgsql;
Lines 1-4 create the table for caching the Fibonacci sequence. The
num column represents the sequence number for which the corresponding Fibonacci number is stored in the
fib column. The
num column is a primary key because it should be unique.
fib_cached() function defined from lines 6-28 introduces quite a bit more syntax. The first line with something new is line five's
DECLARE statement. As you may have ascertained by the previous discussion of argument aliases, this statement introduces a block for declaring variables for use in the function body. All variables used in the function but not declared in the argument signature must be declared here. You can give them a preliminary assignment using the PL/pgSQL assignment operator,
:= (so named to avoid conflicts with the SQL
= comparison operator). You can use any PostgreSQL data type for your variables, but this example again keeps things quite simple. There is a single integer variable,
ret, which keeps track of a value for the function to return.
BEGIN statement on line 11 ends the variable declaration block and starts the function body. Line 12 contains the familiar
IF-THEN block that once again short-circuits the function if the argument to the function (stored in
fib_for) is less than two. Then things get more interesting.
As shown in the
DECLARE block, you can assign a value to a PL/pgSQL variable using
:=, but what if you want to assign a value from a
SELECT statement to a variable? Lines 16-18 demonstrate the approach. A variation of the standard
SELECT INTO statement allows you to select values into one or more PL/pgSQL variables rather than into a table. A comma-delimited list of variables follows the
INTO expression, while the rest of the statement remains a normal
SELECT statement. There are a couple of caveats to
SELECT INTO assignment, however: the
SELECT statement must return no more than one row, and the selected columns must match the number and types of the variables.
Here it's relatively straightforward. The code looks in its cache (the
fib_cache table) to see if it has already calculated and cached the Fibonacci number for the sequence number
SELECT statement selects the
fib column from
fib_cached where the number is
fib_for and stores the result in the
Now, I mentioned that the
SELECT INTO statement can return no more than one row, which also means that it can return zero rows. If this is the case, then the value of
ret will be
NULL in this function. Accordingly, line 20 begins an
IF-THEN block by checking to see if the value of
ret is indeed
NULL. If it is, the function needs to calculate that Fibonacci number. Line 21 thus recursively calls
fib_cached() just as
fib() used recursion. Instead of returning the value, the code uses the
:= assignment operator to store the value in
With the new value calculated, the code needs to insert it into the cache so that it never has to be calculated again. Lines 22-23 do just that with a standard SQL
INSERT statement. The variables
ret can be used right in the
INSERT statement, just as
fib_for was used in the
SELECT statement at lines 16-18. One of the great features of PL/pgSQL is that it precompiles SQL statements embedded in it, using variables as if they were passed to a prepared SQL statement as arguments for placeholders. In other words, the
INSERT statement magically becomes akin to:
PREPARE some_insert(integer, integer) AS INSERT INTO fib_cache (num, fib) VALUES ($1, $2); EXECUTE some_insert(fib_for, ret);
The great thing about this feature of PL/pgSQL is that it makes embedded SQL statements extremely fast. The database can reuse the precompiled query plan for each call to the function (on a per-connection basis) without recompiling and planning.
At any rate, line 25 returns the value of
ret, regardless of where it came from. Adding the caching support makes the function far faster:
try=% explain analyze select fib_cached(26); QUERY PLAN -------------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) (actual time=50.837..50.838 rows=1 loops=1) Total runtime: 50.889 ms (2 rows) try=% explain analyze select fib_cached(26); QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=2.197..2.198 rows=1 loops=1) Total runtime: 2.249 ms (2 rows)
The first call to
fib_cached() finds no cached values, and so it must create them all as it goes along. This simply means that it needs to calculate the values for each number up to 26 once, practically eliminating that recursion (indeed,
fib_cached() has only 24 recursive calls, once each for the numbers 2 through 26). The result is a much faster query: only .05 seconds, as opposed to the nearly 3.8 seconds for
fib(). Of course the second call to
fib_cached() needs only to look up and return the 26th Fibonacci number, because the first call has already cached it. That cuts the time down to 0.0025 seconds. Not bad, eh?
Of course, memoization is not necessarily the best way to speed up a function. Some languages provide memoization support natively or via easily added libraries, but PL/pgSQL offers no such facility. This means adding a fair bit of code to memoize the function. You have something fast, but also something slightly more difficult to maintain.
There's another approach to determining the Fibonacci number for a particular position in the sequence that involves neither recursion nor memoization. Execute a loop
fib_for number of times and keep track of the calculation each time through. How does that look?
CREATE OR REPLACE FUNCTION fib_fast( 1 fib_for integer 2 ) RETURNS integer AS $$ 3 DECLARE 4 ret integer := 0; 5 nxt integer := 1; 6 tmp integer; 7 BEGIN 8 FOR num IN 1..fib_for LOOP 9 tmp := ret; 10 ret := nxt; 11 nxt := tmp + nxt; 12 END LOOP; 13 14 RETURN ret; 15 END; 16 $$ LANGUAGE plpgsql; 17
Everything should look familiar up through line eight, but notice the declaration of multiple variables in the
DECLARE block and the initial values assigned to two of them. The new variables,
tmp, will track the Fibonacci numbers through each iteration of the loop.
Speaking of the loop, it begins on line nine. All loops in PL/pgSQL use the
LOOP keyword and end with
END LOOP. A loop can begin with nothing more than
LOOP, in which case it will be an infinite loop (break out of it with the
EXIT keyword). Otherwise, there are a couple of different approaches to looping in PL/pgSQL, including
WHILE (such as
WHILE foo IS NULL LOOP) and
FOR loop is the only context in PL/pgSQL where you can use a variable without predeclaring it in the
DECLARE block. The form used here (there are other forms--for iterating over rows in a
SELECT query, for example), uses the variable
num, which is automatically created as a read-only integer variable that exists only in the scope of the loop, to loop
fib_for times. This example doesn't actually use
num in the loop, but I thought you should know that it could.
The only thing that happens inside the loop is assignment. The
ret variable once again keeps track of the return value, while
nxt track the previous and next values in the sequence. That's it. Once the loop has run through all of its iterations, the function returns the last value stored in
How does the use of the loop affect performance?
try=% explain analyze select fib_fast(26); QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.433..0.433 rows=1 loops=1) Total runtime: 0.485 ms (2 rows)
It's over four times faster than the cached version, mainly because there are no longer any queries to an external table. It'll be faster with lower numbers and slower with higher numbers because the
fib_for argument determines the number of iterations required. (Any number over 45 won't work at all because the return values will be too big for PostgreSQL integers. If you need them that big, use
Of course these functions are not very practical (except as teaching examples), unless for some reason you need to calculate Fibonacci numbers in your database. The real advantages of PL/pgSQL become apparent when you use it to simplify situations where client code must typically make numerous database calls to satisfy a data pattern. In the interests of illustrating such practical PL/pgSQL, my next article will demonstrate how to write PL/pgSQL functions to simplify the management of ordered many-to-many relationships.
My thanks for David Fetter for suggesting the memoized version of
fib() as an illustrative example for this article, and to Mark Jason Dominus and his terrific Higher Order Perl for an excellent discussion and examples of the Fibonacci sequence functions. I'd also like to thank AndrewSN for providing feedback on a draft of this article.
David Wheeler is a developer at Portland, Oregon-based Values of n, where he writes the code that makes Stikkit's little yellow notes think.
Return to ONLamp.com.
Copyright © 2009 O'Reilly Media, Inc.