doc/src/sgml/ref/create_language.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATELANGUAGE">
   7  <refmeta>
   8   <refentrytitle id="sql-createlanguage-title">CREATE LANGUAGE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE LANGUAGE</refname>
  15   <refpurpose>define a new procedural language</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createlanguage">
  19   <primary>CREATE LANGUAGE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
  25 CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
  26     HANDLER <replaceable class="parameter">call_handler</replaceable> [ VALIDATOR <replaceable>valfunction</replaceable> ]
  27 </synopsis>
  28  </refsynopsisdiv>
  29
  30  <refsect1 id="sql-createlanguage-description">
  31   <title>Description</title>
  32
  33   <para>
  34    Using <command>CREATE LANGUAGE</command>, a
  35    <productname>PostgreSQL</productname> user can register a new
  36    procedural language with a <productname>PostgreSQL</productname>
  37    database.  Subsequently, functions and trigger procedures can be
  38    defined in this new language.
  39   </para>
  40
  41   <para>
  42    <command>CREATE LANGUAGE</command> effectively associates the
  43    language name with a call handler that is responsible for executing
  44    functions written in the language.  Refer to <xref linkend="xplang">
  45    for more information about language call handlers.
  46   </para>
  47
  48   <para>
  49    There are two forms of the <command>CREATE LANGUAGE</command> command.
  50    In the first form, the user supplies just the name of the desired
  51    language, and the <productname>PostgreSQL</productname> server consults
  52    the <link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link>
  53    system catalog to determine the correct parameters.  In the second form,
  54    the user supplies the language parameters along with the language name.
  55    The second form can be used to create a language that is not defined in
  56    <structname>pg_pltemplate</>, but this approach is considered obsolescent.
  57   </para>
  58
  59   <para>
  60    When the server finds an entry in the <structname>pg_pltemplate</> catalog
  61    for the given language name, it will use the catalog data even if the
  62    command includes language parameters.  This behavior simplifies loading of
  63    old dump files, which are likely to contain out-of-date information
  64    about language support functions.
  65   </para>
  66
  67   <para>
  68    Ordinarily, the user must have the
  69    <productname>PostgreSQL</productname> superuser privilege to
  70    register a new language.  However, the owner of a database can register
  71    a new language within that database if the language is listed in
  72    the <structname>pg_pltemplate</structname> catalog and is marked
  73    as allowed to be created by database owners (<structfield>tmpldbacreate</>
  74    is true).  The default is that trusted languages can be created
  75    by database owners, but this can be adjusted by superusers by modifying
  76    the contents of <structname>pg_pltemplate</structname>.
  77    The creator of a language becomes its owner and can later
  78    drop it, rename it, or assign it to a new owner.
  79   </para>
  80  </refsect1>
  81
  82  <refsect1 id="sql-createlanguage-parameters">
  83   <title>Parameters</title>
  84
  85    <variablelist>
  86     <varlistentry>
  87      <term><literal>TRUSTED</literal></term>
  88
  89      <listitem>
  90       <para>
  91        <literal>TRUSTED</literal> specifies that the call handler for
  92        the language is safe, that is, it does not offer an
  93        unprivileged user any functionality to bypass access
  94        restrictions. If this key word is omitted when registering the
  95        language, only users with the
  96        <productname>PostgreSQL</productname> superuser privilege can
  97        use this language to create new functions.
  98       </para>
  99      </listitem>
 100     </varlistentry>
 101
 102     <varlistentry>
 103      <term><literal>PROCEDURAL</literal></term>
 104
 105      <listitem>
 106       <para>
 107        This is a noise word.
 108       </para>
 109      </listitem>
 110     </varlistentry>
 111
 112     <varlistentry>
 113      <term><replaceable class="parameter">name</replaceable></term>
 114
 115      <listitem>
 116       <para>
 117        The name of the new procedural language.  The language name is
 118        case insensitive. The name must be unique among the languages
 119        in the database.
 120       </para>
 121
 122       <para>
 123        For backward compatibility, the name can be enclosed by single
 124        quotes.
 125       </para>
 126      </listitem>
 127     </varlistentry>
 128
 129     <varlistentry>
 130      <term><literal>HANDLER</literal> <replaceable class="parameter">call_handler</replaceable></term>
 131
 132      <listitem>
 133       <para>
 134        <replaceable class="parameter">call_handler</replaceable> is
 135        the name of a previously registered function that will be
 136        called to execute the procedural language functions.  The call
 137        handler for a procedural language must be written in a compiled
 138        language such as C with version 1 call convention and
 139        registered with <productname>PostgreSQL</productname> as a
 140        function taking no arguments and returning the
 141        <type>language_handler</type> type, a placeholder type that is
 142        simply used to identify the function as a call handler.
 143       </para>
 144      </listitem>
 145     </varlistentry>
 146
 147     <varlistentry>
 148      <term><literal>VALIDATOR</literal> <replaceable class="parameter">valfunction</replaceable></term>
 149
 150      <listitem>
 151       <para>
 152        <replaceable class="parameter">valfunction</replaceable> is the
 153        name of a previously registered function that will be called
 154        when a new function in the language is created, to validate the
 155        new function.
 156        If no
 157        validator function is specified, then a new function will not
 158        be checked when it is created.
 159        The validator function must take one argument of
 160        type <type>oid</type>, which will be the OID of the
 161        to-be-created function, and will typically return <type>void</>.
 162       </para>
 163
 164       <para>
 165        A validator function would typically inspect the function body
 166        for syntactical correctness, but it can also look at other
 167        properties of the function, for example if the language cannot
 168        handle certain argument types.  To signal an error, the
 169        validator function should use the <function>ereport()</function>
 170        function.  The return value of the function is ignored.
 171       </para>
 172      </listitem>
 173     </varlistentry>
 174    </variablelist>
 175
 176   <para>
 177    The <literal>TRUSTED</> option and the support function name(s) are
 178    ignored if the server has an entry for the specified language
 179    name in <structname>pg_pltemplate</>.
 180   </para>
 181  </refsect1>
 182
 183  <refsect1 id="sql-createlanguage-notes">
 184   <title>Notes</title>
 185
 186   <para>
 187    The <xref linkend="app-createlang"> program is a simple wrapper around
 188    the <command>CREATE LANGUAGE</> command.  It eases
 189    installation of procedural languages from the shell command line.
 190   </para>
 191
 192   <para>
 193    Use <xref linkend="sql-droplanguage" endterm="sql-droplanguage-title">, or better yet the <xref
 194    linkend="app-droplang"> program, to drop procedural languages.
 195   </para>
 196
 197   <para>
 198    The system catalog <classname>pg_language</classname> (see <xref
 199    linkend="catalog-pg-language">) records information about the
 200    currently installed languages.  Also, <command>createlang</command>
 201    has an option to list the installed languages.
 202   </para>
 203
 204   <para>
 205    To create functions in a procedural language, a user must have the
 206    <literal>USAGE</literal> privilege for the language.  By default,
 207    <literal>USAGE</> is granted to <literal>PUBLIC</> (i.e., everyone)
 208    for trusted languages.  This can be revoked if desired.
 209   </para>
 210
 211   <para>
 212    Procedural languages are local to individual databases.
 213    However, a language can be installed into the <literal>template1</literal>
 214    database, which will cause it to be available automatically in
 215    all subsequently-created databases.
 216   </para>
 217
 218   <para>
 219    The call handler function and the validator function (if any)
 220    must already exist if the server does not have an entry for the language
 221    in <structname>pg_pltemplate</>.  But when there is an entry,
 222    the functions need not already exist;
 223    they will be automatically defined if not present in the database.
 224    (This might result in <command>CREATE LANGUAGE</> failing, if the
 225    shared library that implements the language is not available in
 226    the installation.)
 227   </para>
 228
 229   <para>
 230    In <productname>PostgreSQL</productname> versions before 7.3, it was
 231    necessary to declare handler functions as returning the placeholder
 232    type <type>opaque</>, rather than <type>language_handler</>.
 233    To support loading
 234    of old dump files, <command>CREATE LANGUAGE</> will accept a function
 235    declared as returning <type>opaque</>, but it will issue a notice and
 236    change the function's declared return type to <type>language_handler</>.
 237   </para>
 238  </refsect1>
 239
 240  <refsect1 id="sql-createlanguage-examples">
 241   <title>Examples</title>
 242
 243   <para>
 244    The preferred way of creating any of the standard procedural languages
 245    is just:
 246 <programlisting>
 247 CREATE LANGUAGE plpgsql;
 248 </programlisting>
 249   </para>
 250
 251   <para>
 252    For a language not known in the <structname>pg_pltemplate</> catalog, a
 253    sequence such as this is needed:
 254 <programlisting>
 255 CREATE FUNCTION plsample_call_handler() RETURNS language_handler
 256     AS '$libdir/plsample'
 257     LANGUAGE C;
 258 CREATE LANGUAGE plsample
 259     HANDLER plsample_call_handler;
 260 </programlisting>
 261   </para>
 262  </refsect1>
 263
 264  <refsect1 id="sql-createlanguage-compat">
 265   <title>Compatibility</title>
 266
 267   <para>
 268    <command>CREATE LANGUAGE</command> is a
 269    <productname>PostgreSQL</productname> extension.
 270   </para>
 271  </refsect1>
 272
 273  <refsect1>
 274   <title>See Also</title>
 275
 276   <simplelist type="inline">
 277    <member><xref linkend="sql-alterlanguage" endterm="sql-alterlanguage-title"></member>
 278    <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
 279    <member><xref linkend="sql-droplanguage" endterm="sql-droplanguage-title"></member>
 280    <member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
 281    <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
 282    <member><xref linkend="app-createlang" endterm="app-createlang-title"></member>
 283    <member><xref linkend="app-droplang" endterm="app-droplang-title"></member>
 284   </simplelist>
 285  </refsect1>
 286 </refentry>