create_language.sgml

<refentry id="SQL-CREATELANGUAGE">
 <refmeta>
  <refentrytitle>
   CREATE LANGUAGE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   CREATE LANGUAGE
  </refname>
  <refpurpose>
   Defines a new language for functions
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-09-09</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE [ TRUSTED ] PROCEDURAL LANGUAGE '<replaceable class="parameter">langname</replaceable>'
    HANDLER <replaceable class="parameter">call_handler</replaceable>
    LANCOMPILER '<replaceable class="parameter">comment</replaceable>'
  </synopsis>
  
  <refsect2 id="R2-SQL-CREATELANGUAGE-1">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term>TRUSTED</term>
      <listitem>
       <para>
	<function> TRUSTED</function> specifies that the call handler for
	the language is safe; that is, it offers an unprivileged user
	no functionality to bypass access restrictions. If
	this keyword is omitted when registering the language,
	only users with the <productname>Postgres</productname>
	superuser privilege can use
	this language to create new functions
	(like the 'C' language).  
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">langname</replaceable></term>
      <listitem>
       <para>   
	The name of the new procedural language.
	The language name is case insensitive. A procedural 
	language cannot override one of the built-in languages of
	<productname>Postgres</productname>.
       </para>
      </listitem>
     </varlistentry> 
     <varlistentry>
      <term>HANDLER <replaceable class="parameter">call_handler</replaceable></term>
      <listitem>
       <para>
	<replaceable class="parameter">call_handler</replaceable> is the name
	of a previously
	registered function that will be called to execute the PL
	procedures.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">comment</replaceable></term>
      <listitem>
       <para>
	The <function>LANCOMPILER</function> argument is the
	string that will be
	inserted in the <literal>LANCOMPILER</literal> attribute
	of the new
	<filename>pg_language</filename> entry. At present,
	<productname>Postgres</productname> does not use
	this attribute in any way.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
   
  </refsect2>
  
  <refsect2 id="R2-SQL-CREATELANGUAGE-2">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
CREATE
       </computeroutput></term>
      <listitem>
       <para>
	This message is returned if the language is successfully
	created.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><computeroutput>
ERROR:  PL handler function <replaceable class="parameter">funcname</replaceable>() doesn't exist
       </computeroutput></term>
      <listitem>
       <para>
	This error is returned if the function
	<replaceable class="parameter">funcname</replaceable>()
	is not found.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-CREATELANGUAGE-1">
  <refsect1info>
   <date>1998-09-09</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   Using <command>CREATE LANGUAGE</command>, a 
   <productname>Postgres</productname> user can register
   a new language with <productname>Postgres</productname>. 
   Subsequently, functions and
   trigger procedures can be defined in this new language.
   The user must have the <productname>Postgres</productname>
   superuser privilege to
   register a new language.
  </para>

  <refsect2 id="R2-SQL-CREATELANGUAGE-3">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Writing PL handlers
   </title>
   <para>
    The call handler for a procedural language must be written
    in a compiler language such as 'C' and registered with
    <productname>Postgres</productname> as a function taking
    no arguments and returning the
    <type>opaque</type> type, a placeholder for unspecified or undefined types..
    This prevents the call handler from being
    called directly as a function from queries.
   </para>
   <para>
    However, arguments must be supplied on the actual call when a
    PL function or trigger
    procedure in the language offered by the handler is to be
    executed.
    <itemizedlist>
     <listitem>
      <para>
       When called from the trigger manager, the only argument is
       the object ID from the procedure's <filename>pg_proc</filename>
       entry. All other
       information from the trigger manager is found in the
       global <structname>CurrentTriggerData</structname> pointer.
      </para>
     </listitem>
     <listitem>
      <para>
       When called from the function manager, the arguments are
       the object ID of the procedure's <filename>pg_proc</filename>
       entry, the number
       of arguments given to the PL function, the arguments in a
       <structname>FmgrValues</structname> structure and a pointer
       to a boolean where the
       function tells the caller if the return value is the SQL
       NULL value.
      </para>
     </listitem>
    </itemizedlist>
   </para>
   <para>
    It's up to the call handler to fetch the
    <filename>pg_proc</filename> entry and
    to analyze the argument and return types of the called
    procedure. The AS clause from the
    <command>CREATE FUNCTION</command> of
    the procedure will be found in the <literal>prosrc</literal>
    attribute of the
    <filename>pg_proc</filename> 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.
   </para>
  </refsect2>
   
  <refsect2 id="R2-SQL-CREATELANGUAGE-4">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    Use <command>CREATE FUNCTION</command>
    to create a function.
   </para>
   <para>
    Use <command>DROP LANGUAGE</command>  to drop procedural languages.
   </para>
   <para>
    Refer to the table <filename>pg_language</filename>
    for further information:
    <programlisting>
     <computeroutput>
Table    = pg_language
+--------------------------+--------------------------+-------+
|          Field           |          Type            | Length|
+--------------------------+--------------------------+-------+
| lanname                  | name                     |    32 |
| lancompiler              | text                     |   var |
+--------------------------+--------------------------+-------+

lanname |lancompiler   
--------+--------------
internal|n/a           
lisp    |/usr/ucb/liszt
C       |/bin/cc       
sql     |postgres
     </computeroutput>
    </programlisting>

   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-CREATELANGUAGE-4">
  <title>
   Restrictions
  </title>
  <para>
   Since the call handler for a procedural language must be
   registered with <productname>Postgres</productname> in the 'C' language,
   it inherits
   all the capabilities and restrictions of 'C' functions.
  </para>
 </refsect1>
 <refsect1 id="R1-SQL-CREATELANGUAGE-5">
  <title>
   Bugs
  </title>
  <para> 
   At present, the definitions for a procedural language cannot be
   changed once they have been created.
  </para>
 </refsect1>
 
 <refsect1 id="R1-SQL-CREATELANGUAGE-6">
  <title>
   Usage
  </title>
  <para>
   This is a template for a PL handler written in 'C':
   <programlisting>
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/elog.h"
#include "fmgr.h"        /* for FmgrValues struct */
#include "access/heapam.h"
#include "utils/syscache.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"

Datum
plsample_call_handler(
     Oid       prooid,
     int       pronargs,
     FmgrValues     *proargs,
     bool      *isNull)
{
     Datum          retval;
     TriggerData    *trigdata;

     if (CurrentTriggerData == NULL) {
          /*
           * Called as a function
           */

          retval = ...
     } else {
          /*
           * Called as a trigger procedure
           */
          trigdata = CurrentTriggerData;
          CurrentTriggerData = NULL;

          retval = ...
     }

     *isNull = false;
     return retval;
}
   </programlisting>
  </para>

  <para>
   Only a few thousand lines of code have to be added instead
   of the dots to complete the PL call handler. 
   See <command>CREATE FUNCTION</command> for information on how to compile
   it into a loadable module.
  </para>
  <para>
   The following commands then register the sample procedural
   language:
   <programlisting>
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';
   </programlisting>
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-CREATELANGUAGE-7">
  <title>
   Compatibility
  </title>
  <para>
   CREATE LANGUAGE is a <productname>Postgres</productname> extension.
  </para>

  <refsect2 id="R2-SQL-CREATELANGUAGE-5">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
    There is no <command>CREATE LANGUAGE</command> statement in
    <acronym>SQL92</acronym>.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->