Programming languages are made available to databases by being created as a database object. You will therefore need to add the PL/pgSQL language to your database before you can use it (it is installed with PostgreSQL by default). The following steps demonstrate how to add PL/pgSQL to an existing database.
To add PL/pgSQL to your
PostgreSQL database, you can either use the createlang application from
the command line, or the CREATE LANGUAGE
SQL command from within a database
client such as psql. The use of the CREATE LANGUAGE
command first requires the creation of the PL/pgSQL call handler, which
is the function that actually processes and interprets the PL/pgSQL code.
Though the createlang utility is simpler to use, as it abstracts the creation of the call handler and the language away from the user, the following sections document both methods.
Installing PL/pgSQL in the template1
database causes all subsequent
databases that are created with template1
as their template (which is the
default) to also have PL/pgSQL installed.
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 without any output upon successful execution.
3.128.200.220