Red Hat Database allows users to add new programming languages, making them available for writing functions and procedures. These are called Procedural Languages (PL). In the case of a function or trigger procedure written in a procedural language, the database server has no built-in knowledge about how to interpret the function's source text. Instead, the task is passed to a special handler that knows the details of the language. The handler could either do all the work of parsing, syntax analysis, execution, and so on itself, or it could serve as "glue" between Red Hat Database and an existing implementation of a programming language. The handler is a special programming language function that is compiled into a shared object and loaded on demand.
Writing a handler for a new procedural language is outside the scope of this documentation, although some information is provided in the CREATE LANGUAGE reference page of the Red Hat Database SQL Guide and Reference. Several procedural languages are available in the standard Red Hat Database distribution.
Although a procedural language must be "installed" into each database where it is to be used, procedural languages that are installed in the template1 database are automatically available in all subsequently created databases. Thus the database administrator can decide which languages are available in particular databases, and which languages are available to all databases.
For the languages supplied with the standard distribution, the shell script createlang can be used to install a language in a database, instead of carrying out the details by hand. For example, to install PL/pgSQL into the template1 database, use
createlang plpgsql template1 |
Manual Procedural Language Installation
A procedural language is installed in the database in three steps that must be carried out by a database superuser.
The shared object for the language handler must be compiled and installed into an appropriate library directory. This works in the same way as building and installing modules with regular user-defined C functions; refer to the Compiling and Linking Dynamically-Loaded Functions chapter in the Red Hat Database Programmer's Guide.
The handler must be declared with the command
CREATE FUNCTION handler_function_name () RETURNS OPAQUE AS 'path-to-shared-object' LANGUAGE 'C'; |
The PL must be declared with the command
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name HANDLER handler_function_name; |
In a default Red Hat Database installation, the handler for the PL/pgSQL language is built and installed into the library directory. If Tcl/Tk support is configured in, the handler for PL/Tcl is also built and installed in the same location. Likewise, the PL/Perl handler is built and installed if Perl support is configured. The createlang script automates steps step 2 and step 3 described above.
Example 6-1. Manual Installation of PL/pgSQL
The following command tells the database server where to find the shared object for the PL/pgSQL language's call handler function.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '$libdir/plpgsql.so' LANGUAGE 'C'; |
The command
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; |
For more information on the CREATE FUNCTION and CREATE LANGUAGE commands, refer to the Red Hat Database SQL Guide and Reference.