Red Hat Database: Administrator and User's Guide
PrevNext

Chapter 6. Procedural Languages

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.

Installing Procedural Languages

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
The manual procedure described below is recommended for installing only custom languages that createlang does not know about. You can find more information about the createlang utility in the reference section of this manual.

Manual Procedural Language Installation

A procedural language is installed in the database in three steps that must be carried out by a database superuser.

  1. 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.

  2. The handler must be declared with the command 
    CREATE FUNCTION handler_function_name ()
    RETURNS OPAQUE AS
    'path-to-shared-object' LANGUAGE 'C';
    The special return type of OPAQUE tells the database that this function does not return one of the defined SQL data types and is not directly usable in SQL statements.

  3. The PL must be declared with the command 
    CREATE [TRUSTED] [PROCEDURAL] 
LANGUAGE language-name
    HANDLER handler_function_name;
    The optional key word TRUSTED tells whether ordinary database users that have no superuser privileges should be allowed to use this language to create functions and trigger procedures. Since PL functions are executed inside the database backend, the TRUSTED flag should be given only for languages that do not allow access to database backends internals or the file system. The languages PL/pgSQL, PL/Tcl, and PL/Perl are known to be trusted; the languages PL/TclU and PL/PerlU are designed to provide unlimited functionality should not be marked trusted.

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';
then defines that the previously declared call handler function should be invoked for functions and trigger procedures where the language attribute is plpgsql.

For more information on the CREATE FUNCTION and CREATE LANGUAGE commands, refer to the Red Hat Database SQL Guide and Reference.

PrevHomeNext
Functions and Triggers Backup and Restore