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>
	  <function>TRUSTED</function>
	 </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>
	  <ReturnValue>CREATE</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   This message is returned if the language is successfully
	   created.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
      <TERM>
       <ReturnValue>ERROR:  PL handler function <replaceable class="parameter">funcname</replaceable>() doesn't exist</ReturnValue>
      </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':
  </PARA>
  <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>
   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:t
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:
-->