CREATE LANGUAGE - Defines a new language for functions


SYNOPSIS

       CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'langname'
           HANDLER call_handler
           LANCOMPILER 'comment'

   INPUTS
       TRUSTED
              TRUSTED  specifies  that  the  call handler for the
              language is safe; that is, it  offers  an  unprivi­
              leged   user  no  functionality  to  bypass  access
              restrictions. If this keyword is omitted when  reg­
              istering the language, only users with the Postgres
              superuser privilege can use this language to create
              new functions.

       langname
              The  name of the new procedural language.  The lan­
              guage name is case insensitive. A  procedural  lan­
              guage cannot override one of the built-in languages
              of Postgres.

       HANDLER call_handler
              call_handler is the name of a previously registered
              function that will be called to execute the PL pro­
              cedures.

       comment
              The LANCOMPILER argument is the string that will be
              inserted  in  the  LANCOMPILER attribute of the new
              pg_language entry. At present,  Postgres  does  not
              use this attribute in any way.

   OUTPUTS
       CREATE This  message  is  returned if the language is suc­
              cessfully created.

       ERROR: PL handler function funcname() doesn't exist
              This error is returned if the  function  funcname()
              is not found.


DESCRIPTION

       Using  CREATE LANGUAGE, a Postgres user can register a new
       language with Postgres.  Subsequently, functions and trig­
       ger  procedures  can be defined in this new language.  The
       user must have the Postgres superuser privilege to  regis­
       ter a new language.

              Note: In Postgres 7.1 and later, call handlers must
              adhere to the "version 1" function  manager  inter­
              face, not the old-style interface.

       The call handler for a procedural language must be written
       in a compiled language such as C and registered with Post­
       gres  as  a function taking no arguments and returning the
       opaque type, a placeholder for  unspecified  or  undefined
       types.   This  prevents the call handler from being called
       directly as a function from queries.  (However,  arguments
       may  be  supplied in the actual call when a PL function in
       the language offered by the handler is to be executed.)

       The call handler is called in the same way  as  any  other
       function:  it receives a pointer to a FunctionCallInfoData
       struct containing argument values  and  information  about
       the  called function, and it is expected to return a Datum
       result (and possibly set the isnull field of the Function­
       CallInfoData  struct,  if  it wishes to return an SQL NULL
       result). The difference between  a  call  handler  and  an
       ordinary  callee function is that the flinfo->fn_oid field
       of the FunctionCallInfoData struct will contain the OID of
       the  PL  function  to  be  called, not of the call handler
       itself. The call handler must use this field to  determine
       which  function to execute. Also, the passed argument list
       has been set up according to the declaration of the target
       PL function, not of the call handler.

       It's up to the call handler to fetch the pg_proc entry and
       to analyze the argument and return  types  of  the  called
       procedure.  The  AS clause from the CREATE FUNCTION of the
       procedure will be found in the  prosrc  attribute  of  the
       pg_proc  table  entry.  This may be the source text in the
       procedural language itself (like for PL/Tcl),  a  pathname
       to  a  file,  or anything else that tells the call handler
       what to do in detail.

       Often, the same function is  called  many  times  per  SQL
       statement.   A  call handler can avoid repeated lookups of
       information  about  the  called  function  by  using   the
       flinfo->fn_extra  field.  This will initially be NULL, but
       can be set by the call handler  to  point  at  information
       about   the   PL   function.   On   subsequent  calls,  if
       flinfo->fn_extra is already non-NULL then it can  be  used
       and  the information lookup step skipped. The call handler
       must be careful that flinfo->fn_extra is made to point  at
       memory  that  will live at least until the end of the cur­
       rent query, since an FmgrInfo data structure could be kept
       that  long.   One  way to do this is to allocate the extra
       data in the memory context specified  by  flinfo->fn_mcxt;
       such  data  will  normally  have  the same lifespan as the
       FmgrInfo itself. But the handler could also choose to  use

       nition information across queries.

       When a PL function is invoked as a  trigger,  no  explicit
       arguments  are passed, but the FunctionCallInfoData's con­
       text field points at a TriggerData node, rather than being
       NULL  as  it  is  in  a plain function call.  A PL handler
       should provide mechanisms for PL functions to get  at  the
       trigger information.

   NOTES
       Use CREATE FUNCTION to create a function.

       Use DROP LANGUAGE to drop procedural languages.

       Refer to the table pg_language for further information:

               Table "pg_language"
          Attribute   |  Type   | Modifier
       ---------------+---------+----------
        lanname       | name    |
        lanispl       | boolean |
        lanpltrusted  | boolean |
        lanplcallfoid | oid     |
        lancompiler   | text    |

          lanname   | lanispl | lanpltrusted | lanplcallfoid | lancompiler
       -------------+---------+--------------+---------------+-------------
        internal    | f       | f            |             0 | n/a
        C           | f       | f            |             0 | /bin/cc
        sql         | f       | f            |             0 | postgres

       The  call  handler for a procedural language must normally
       be written in C and registered as 'internal' or  'C'  lan­
       guage,  depending on whether it is linked into the backend
       or dynamically loaded.  The call handler  cannot  use  the
       old-style 'C' function interface.

       At present, the definitions for a procedural language can­
       not be changed once they have been created.


USAGE

       This is a template for a PL handler written in C:

       #include "executor/spi.h"
       #include "commands/trigger.h"
       #include "utils/elog.h"
       #include "fmgr.h"
       #include "access/heapam.h"
       #include "utils/syscache.h"
       #include "catalog/pg_proc.h"
       #include "catalog/pg_type.h"


       Datum
       plsample_call_handler(PG_FUNCTION_ARGS)
       {
            Datum          retval;

            if (CALLED_AS_TRIGGER(fcinfo))
            {
                 /*
                  * Called as a trigger procedure
                  */
                 TriggerData    *trigdata = (TriggerData *) fcinfo->context;

                 retval = ...
            } else {
                 /*
                  * Called as a function
                  */

                 retval = ...
            }

            return retval;
       }

       Only a few thousand lines of code have to be added instead
       of  the  dots to complete the PL call handler.  See CREATE
       FUNCTION for information on how to compile it into a load­
       able module.

       The following commands then register the sample procedural
       language:

       CREATE FUNCTION plsample_call_handler () RETURNS opaque
           AS '/usr/local/pgsql/lib/plsample.so'
           LANGUAGE 'C';
       CREATE PROCEDURAL LANGUAGE 'plsample'
           HANDLER plsample_call_handler
           LANCOMPILER 'PL/Sample';


COMPATIBILITY

   SQL92
       CREATE LANGUAGE is a Postgres extension.  There is no CRE­
       ATE LANGUAGE statement in SQL92.


Man(1) output converted with man2html