<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_language.sgml,v 1.30 2003/01/19 00:13:29 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-CREATELANGUAGE">
 <refmeta>
  <refentrytitle id="sql-createlanguage-title">CREATE LANGUAGE</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE LANGUAGE</refname>
  <refpurpose>define a new procedural language</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">langname</replaceable>
    HANDLER <replaceable class="parameter">call_handler</replaceable> [ VALIDATOR <replaceable>valfunction</replaceable> ]
</synopsis>
 </refsynopsisdiv>

 <refsect1 id="sql-createlanguage-description">
  <title>Description</title>

  <para>
   Using <command>CREATE LANGUAGE</command>, a
   <productname>PostgreSQL</productname> user can register a new
   procedural language with a <productname>PostgreSQL</productname>
   database.  Subsequently, functions and trigger procedures can be
   defined in this new language.  The user must have the
   <productname>PostgreSQL</productname> superuser privilege to
   register a new language.
  </para>

  <para>
   <command>CREATE LANGUAGE</command> effectively associates the
   language name with a call handler that is responsible for executing
   functions written in the language.  Refer to the &cite-programmer;
   for more information about language call handlers.
  </para>

  <para>
   Note that procedural languages are local to individual databases.
   To make a language available in all databases by default, it should
   be installed into the <literal>template1</literal> database.
  </para>
 </refsect1>

 <refsect1 id="sql-createlanguage-parameters">
  <title>Parameters</title>

   <variablelist>
    <varlistentry>
     <term><literal>TRUSTED</literal></term>

     <listitem>
      <para>
       <literal>TRUSTED</literal> specifies that the call handler for
       the language is safe, that is, it does not offer an
       unprivileged user any functionality to bypass access
       restrictions. If this keyword is omitted when registering the
       language, only users with the
       <productname>PostgreSQL</productname> superuser privilege can
       use this language to create new functions.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PROCEDURAL</literal></term>

     <listitem>
      <para>
       This is a noise word.
      </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>PostgreSQL</productname>.
      </para>

      <para>
       For backward compatibility, the name may be enclosed by single
       quotes.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>HANDLER</literal> <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 procedural language functions.  The call
       handler for a procedural language must be written in a compiled
       language such as C with version 1 call convention and
       registered with <productname>PostgreSQL</productname> as a
       function taking no arguments and returning the
       <type>language_handler</type> type, a placeholder type that is
       simply used to identify the function as a call handler.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>VALIDATOR</literal> <replaceable class="parameter">valfunction</replaceable></term>

     <listitem>
      <para>
       <replaceable class="parameter">valfunction</replaceable> is the
       name of a previously registered function that will be called
       when a new function in the language is created, to validate the
       new function.
       If no
       validator function is specified, then a new function will not
       be checked when it is created.
       The validator function must take one argument of
       type <type>oid</type>, which will be the OID of the
       to-be-created function, and will typically return <type>void</>.
      </para>

      <para>
       A validator function would typically inspect the function body
       for syntactical correctness, but it can also look at other
       properties of the function, for example if the language cannot
       handle certain argument types.  To signal an error, the
       validator function should use the <function>elog()</function>
       function.  The return value of the function is ignored.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
 </refsect1>

 <refsect1 id="sql-createlanguage-diagnostics">
  <title>Diagnostics</title>

  <msgset>
   <msgentry>
    <msg>
     <msgmain>
      <msgtext>
<screen>
CREATE LANGUAGE
</screen>
      </msgtext>
     </msgmain>
    </msg>
 
    <msgexplan>
     <para>
      This message is returned if the language is successfully
      created.
     </para>
    </msgexplan>
   </msgentry>

   <msgentry>
    <msg>
     <msgmain>
      <msgtext>
<screen>
ERROR:  PL handler function <replaceable class="parameter">funcname</replaceable>() doesn't exist
</screen>
      </msgtext>
     </msgmain>
    </msg>

    <msgexplan>
     <para>
      This error is returned if the function <replaceable
      class="parameter">funcname</replaceable>() is not found.
     </para>
    </msgexplan>
   </msgentry>
  </msgset>
 </refsect1>

 <refsect1 id="sql-createlanguage-notes">
  <title>Notes</title>

  <para>
   This command normally should not be executed directly by users.
   For the procedural languages supplied in the
   <productname>PostgreSQL</productname> distribution, the <xref
   linkend="app-createlang"> script should be used, which will also
   install the correct call handler.  (<command>createlang</command>
   will call <command>CREATE LANGUAGE</command> internally.)
  </para>

  <para>
   In <productname>PostgreSQL</productname> versions before 7.3, it was
   necessary to declare handler functions as returning the placeholder
   type <type>opaque</>, rather than <type>language_handler</>.
   To support loading 
   of old dump files, <command>CREATE LANGUAGE</> will accept a function
   declared as returning <type>opaque</>, but it will issue a NOTICE and
   change the function's declared return type to <type>language_handler</>.
  </para>

  <para>
   Use the <xref linkend="sql-createfunction" endterm="sql-createfunction-title"> command to create a new
   function.
  </para>

  <para>
   Use <xref linkend="sql-droplanguage" endterm="sql-droplanguage-title">, or better yet the <xref
   linkend="app-droplang"> script, to drop procedural languages.
  </para>

  <para>
   The system catalog <classname>pg_language</classname> records
   information about the currently installed procedural languages.

<screen>
        Table "pg_language"
   Attribute   |   Type    | Modifier
---------------+-----------+----------
 lanname       | name      |
 lanispl       | boolean   |
 lanpltrusted  | boolean   |
 lanplcallfoid | oid       |
 lanvalidator  | oid       |
 lanacl        | aclitem[] |

   lanname   | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
-------------+---------+--------------+---------------+--------------+--------
 internal    | f       | f            |             0 |         2246 |
 c           | f       | f            |             0 |         2247 |
 sql         | f       | t            |             0 |         2248 | {=U}
</screen>
  </para>

  <para>
   At present, with the exception of the permissions, the definition 
   of a procedural language cannot be changed once it has been created.
  </para>

  <para>
   To be able to use a procedural language, a user must be granted the
   <literal>USAGE</literal> privilege.  The
   <command>createlang</command> program automatically grants
   permissions to everyone if the language is known to be trusted.
  </para>
 </refsect1>

 <refsect1 id="sql-createlanguage-examples">
  <title>Examples</title>

  <para>
   The following two commands executed in sequence will register a new
   procedural language and the associated call handler.
<programlisting>
CREATE FUNCTION plsample_call_handler () RETURNS language_handler
    AS '$libdir/plsample'
    LANGUAGE C;
CREATE LANGUAGE plsample
    HANDLER plsample_call_handler;
</programlisting>
  </para>
 </refsect1>

 <refsect1 id="sql-createlanguage-compat">
  <title>Compatibility</title>

  <para>
   <command>CREATE LANGUAGE</command> is a
   <productname>PostgreSQL</productname> extension.
  </para>
 </refsect1>

 <refsect1>
  <title>History</title>

  <para>
   The <command>CREATE LANGUAGE</command> command first appeared in
   <productname>PostgreSQL</productname> 6.3.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <para>
   <simplelist type="inline">
    <member><xref linkend="app-createlang"></member>
    <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
    <member><xref linkend="app-droplang"></member>
    <member><xref linkend="sql-droplanguage" endterm="sql-droplanguage-title"></member>
    <member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
    <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
    <member>&cite-programmer;</member>
   </simplelist>
  </para>
 </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:
-->
