September 26, 2024: PostgreSQL 17 Released!
Unsupported versions: 7.0 / 6.5 / 6.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Chapter 49. Procedural Languages

Beginning with the release of version 6.3, Postgres supports the definition of procedural languages. In the case of a function or trigger procedure defined in a procedural language, the database has no builtin knowlege how to interpret the functions source text. Instead, the calls are passed into a handler that knows the details of the language. The handler itself is a special programming language function compiled into a shared object and loaded on demand.

Installing Procedural Languages

Procedural Language Installation

A procedural language is installed in the database in three steps.

  1. The shared object for the language handler must be compiled and installed. By default the handler for PL/pgSQL is built and installed into the database library directory. If Tcl/Tk support is configured in, the handler for PL/Tcl is also built and installed in the same location.

    Writing a handler for a new procedural language (PL) is outside the scope of this manual.

  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 base- or composite 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
            LANCOMPILER 'description';
                    
    
    The optional keyword TRUSTED tells if ordinary database users that have no superuser privileges can use this language to create functions and trigger procedures. Since PL functions are executed inside the database backend it should only be used for languages that don't gain access to database backends internals or the filesystem. The languages PL/pgSQL and PL/Tcl are known to be trusted.

Example

  1. The following command tells the database where to find the shared object for the PL/pgSQL languages call handler function.

        CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
            '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
        
    
  2. 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'.

    PL handler functions have a special call interface that is different from regular C language functions. One of the arguments given to the handler is the object ID in the pg_proc tables entry for the function that should be executed. The handler examines various system catalogs to analyze the functions call arguments and it's return data type. The source text of the functions body is found in the prosrc attribute of pg_proc. Due to this, in contrast to C language functions, PL functions can be overloaded like SQL language functions. There can be multiple different PL functions having the same function name, as long as the call arguments differ.

    Procedural languages defined in the template1 database are automatically defined in all subsequently created databases. So the database administrator can decide which languages are available by default.