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