Adding procedural language support to PostgreSQL

This document was originally written for PostgreSQL 7.0.3 but was updated on 13 October 2001 for
version 7.1.3.

I’m in the process of creating a new version of FreshPorts. This version will track all of the
FreeBSD source tree, not just the ports collection. Initial testing of database
design and strategy was conducted using SybaseSQL Anywhere under
NT. I’ve been using that database for many years. It’s my favorite.
I’ve converted the stored procedures and triggers into PostgreSQL format. This was not a straight
forward exercise. The biggest problem was incorrectly documented functions,
specifically the position function. Only by searching the mailing lists did I
discover it has been renamed to strpos. The function is documented correctly in the
online PostgreSQL documentation
but the documentation supplied with the application was out
of date. Be warned.

After installing Postgres, all of your documentation is available at /usr/local/share/doc/postgresql.

Stored procedures aren’t built into Postgres but are available via loadable modules.
By default, two procedural languages are available with the standard install: PLTCL
and PLSQL. Please refer to the PostgreSQL Programmer’s Guide for more information.
The next section shows how I added that support.

NOTE: your plpgsql.so may not be in that location. Use find or locate to verify. My
latest install put it at /usr/local/lib/plpgsql.so. You could also use this command
to verify the installed location:

then defines that the previously declared call handler function should be invoked for
functions and trigger procedures where the language attribute is ‘plpgsql’.

This section is for version 7.1.3:

This is the command you enter as the pgsql user:

$ createlang plpgsql FreshPorts2

I’ve noticed that if you don’t issue this command as the pgsql user, you’ll see this error
message:

$ createlang plpgsql FreshPorts2
createlang: missing required argument PGLIB directory
(This is the directory where the interpreter for the procedural
language is stored. Traditionally, these are installed in whatever
'lib' directory was specified at configure time.)

PL/pgSQL example

So far, I’ve found this to be a good language to use. But it can be
difficult to get started. A lack of practical examples makes things tough. I
did manage to find a few examples and have provided them here.

Or, you could use the CREATE OR REPLACE FUNCTION phrase
instead of CREATE FUNCTION..

Functions can be overloaded. That means you could have another function ct1 which
takes different arguments.

You can see other example in Chapter 10 of the PostgreSQL User’s Guide.

quotes

Be careful with your quotes. Either use \ or double the quotes to
get them into your procedures. For example:

pathname = \'\';
pathname = '''';

Both of these statements will set pathname to an empty string. Use
whichever format you prefer.

triggers

I found example triggers in Chapter 10 of the PostgreSQL User’s Guide.
In short, you create a function and then create a trigger which calls that
function. The convention seems to be that you give the same name to both the trigger
and the function. Sounds good to me. Here’s a bit from my FreshPorts script
which does just that. This trigger ensures that the ID field of a table does not
change.