CREATE LANGUAGE is the SQL command which adds procedural languages to the
currently connected database. Before it can be used, however, the CREATE FUNCTION
command must first be used to create the procedural call handler.
Here is the syntax to create a PL/pgSQL call handler with CREATE FUNCTION:
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/postgres_library_path/plpgsql.so' LANGUAGE 'C'In this syntax, postgres_library_path is the absolute system path to the installed PostgreSQL
library files. This path, by default, is /usr/local/pgsql/lib. Example 11-1 uses the CREATE FUNCTION command to create the
PL/pgSQL call handler, assuming the plpgsql.so file is in the default location.
Example 11-1. Creating the PL/pgSQL call handler
booktown=# CREATE FUNCTION plpgsql_call_handler ()
booktown-# RETURNS OPAQUE
booktown-# AS '/usr/local/pgsql/lib/plpgsql.so'
booktown-# LANGUAGE 'C';
CREATE
Example 11-1 only creates the function handler; the language itself must also be
added with the CREATE LANGUAGE command. Here is the syntax to add PL/pgSQL to a database:
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL'In this syntax, plpgsql is the name of the language to be created, the
plpgsql_call_handler is the name of the call handler function (e.g., the one created in
Example 11-1), and the PL/pgSQL string constant
following the LANCOMPILER keyword is an arbitrary descriptive note.
Example 11-2 adds PL/pgSQL to the booktown
database with the CREATE LANGUAGE command.
Example 11-2. Adding PL/pgSQL with CREATE LANGUAGE
booktown=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
booktown-# LANCOMPILER 'PL/pgSQL';
CREATE
The name following the HANDLER keyword should be the same name which is used to
create the call handler. Since Example 11-1 created a call handler named
plpgsql_call_handler, Example 11-2 uses the same
name.
The string following the LANCOMPILER keyword is an outdated legacy clause, and its value is not
consequential. Even so, as of PostgreSQL 7.1.x, it is a required clause. It is commonly used as a comment space to
describe the language.
To execute createlang you will first need to be at the command prompt. If the
operating system username you are currently logged into is the same as that of a database superuser account on the target
database, you can call createlang with the command shown in Example 11-3 (you will be asked for a password if the database requires one). Otherwise, to
pass the username of a database superuser to createlang, use the
-U flag as shown in Example 11-4.
Example 11-3. Using createlang as a database superuser
$ cd /usr/local/pgsql/bin
booktown=# createlang plpgsql booktown
Example 11-4. Explicitly passing a superuser account name to createlang
$ cd /usr/local/pgsql/bin/
$ createlang plpgsql -U manager booktown
The createlang program will return you to a shell prompt upon successful execution.
