Installing Procedural Languages

I l @ ve RuBoard

In a default installation, PostgreSQL will automatically include the capability for the system to access code written in the PL/pgSQL language. Both PL/Tcl and PL/Perl can also be included by setting their respective compile-time variables (that is, --witht-cl or --with-perl ).

To enable a specific PL after the system is in production, however, it is necessary to define it through several steps. There are two methods for accomplishing this, either through the explicit declaration of SQL statements or by using the createlang system utility. The createlang utility helps automate many of the steps needed in the manual creation of a new language and is generally the preferred method. Both methods are covered in the following sections.

SQL Declaration

The location of the shared library, which acts as a handler, might vary from installation to installation. RPM-based installations usually place it in the /usr/lib/pgsql directory. Source-based installations will depend on what path was supplied to the install script. The UNIX find command can always be used with great effect in these situations.

Adding a new procedural language to PostgreSQL using explicit SQL statements is accomplished as follows :

1. Compile the shared object (for example, plpgsql.so ) by referring to the documentation included with the source code of the object. Once compiled, copy the object to the appropriate library directory.

2. Declare the handler in PostgreSQL by using the CREATE FUNCTION clause (refer to the "CREATE FUNCTION" section in Chapter 1, "PostgreSQL SQL Reference," for syntax specifics).

   For instance:

    CREATE FUNCTION plpgsql_call_handler()      RETURN OPAQUE AS      '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; 

3. Create the language as a trusted language by using the CREATE LANGUAGE clause (refer to the "CREATE LANGUAGE" section in Chapter 1 for syntax specifics).

For instance:

 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'      HANDLER plpgsql_call_handler      LANCOMPILER 'PL/pgSQL'; 

Using the createlang Utility

Alternatively, languages included in the base PostgreSQL system (such as PL/Tcl) can be defined by utilizing the createlang utility. This utility simplifies many of the preceding steps. (Refer to the createlang command in Chapter 6, "User Executable Files," for specific options.)

The createlang utility can be used currently to register either the PL/Tcl or PL/Perl language with the back-end server. Moreover, the createlang utility also accepts an option to declare what database the language is registered in. If a language is registered in the template1 database, then that language will be available in all future databases subsequently created.

For instance:

 >createlang pltcl template1 

This command will automatically register the PL/Tcl language in the template1 database and in all subsequent databases.

I l @ ve RuBoard