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 Sybase SQL 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
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.
I’ve also updated the original PostgreSQL article with a short bit about backups.
The following steps are found in the
Procedural Languages section
PostgreSQL Interactive Documentation. Try
This section is for version 7.0.3:
The following command tells the database where to find the shared object for the PL/pgSQL language’s call handler function.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
plpgsql.somay 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:
# grep plpgsql.so /var/db/pkg/postgresql-7.1.3/+CONTENTS lib/plpgsql.so
You then add the PREFIX to the path supplied. By default, the PREFIX is
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
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:
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 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 exampleSo 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.
CREATE FUNCTION ct1(text, text) RETURNS text AS ' BEGIN RETURN $1 || $2; END; ' LANGUAGE 'plpgsql';
This function, ct1, takes two strings and concatenates them. Here’s how I saved that function and then used it:
[dan@xeon:~] $ /usr/local/pgsql/bin/psql FreshPorts2 Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit FreshPorts2=# CREATE FUNCTION ct1(text, text) RETURNS text AS ' FreshPorts2'# BEGIN FreshPorts2'# RETURN $1 || $2; FreshPorts2'# END; FreshPorts2'# ' LANGUAGE 'plpgsql'; CREATE FreshPorts2=# select ct1('abc', 'def'); ct1 -------- abcdef (1 row) FreshPorts2=#
If you decide to modify this function, you need to first drop it, then save it. Here’s the message you’ll see if you don’t drop it first:
ERROR: ProcedureCreate: procedure ct1 already exists with same arguments
This is the command you use to drop that function:
drop function ct1(text, text);
Or, you could use the
CREATE OR REPLACE FUNCTION phrase
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.
quotesBe 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.
triggersI 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.
-- the procedure DROP function id_change(); create function id_change() returns OPAQUE as ' begin if (new.id <> old.id) then RAISE EXCEPTION ''modifications to id % with name=% cannot completed as you are not allowed to change id (new value was %).'', old.id, old.name, new.id; end if; RETURN OLD; end; ' LANGUAGE 'plpgsql'; -- the trigger drop trigger id_change on test; create trigger id_change before update on element for each row execute procedure id_change();
Hope that helps to get you started.