doc/src/sgml/xplang.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3  <chapter id="xplang">
   4   <title id="xplang-title">Procedural Languages</title>
   5
   6   <indexterm zone="xplang">
   7    <primary>procedural language</primary>
   8   </indexterm>
   9
  10   <para>
  11    <productname>PostgreSQL</productname> allows user-defined functions
  12    to be written in other languages besides SQL and C.  These other
  13    languages are generically called <firstterm>procedural
  14    languages</firstterm> (<acronym>PL</>s).  For a function
  15    written in a procedural language, the database server has
  16    no built-in knowledge about how to interpret the function's source
  17    text. Instead, the task is passed to a special handler that knows
  18    the details of the language.  The handler could either do all the
  19    work of parsing, syntax analysis, execution, etc. itself, or it
  20    could serve as <quote>glue</quote> between
  21    <productname>PostgreSQL</productname> and an existing implementation
  22    of a programming language.  The handler itself is a
  23    C language function compiled into a shared object and
  24    loaded on demand, just like any other C function.
  25   </para>
  26
  27   <para>
  28    There are currently four procedural languages available in the
  29    standard <productname>PostgreSQL</productname> distribution:
  30    <application>PL/pgSQL</application> (<xref linkend="plpgsql">),
  31    <application>PL/Tcl</application> (<xref linkend="pltcl">),
  32    <application>PL/Perl</application> (<xref linkend="plperl">), and
  33    <application>PL/Python</application> (<xref linkend="plpython">).
  34    There are additional procedural languages available that are not
  35    included in the core distribution. <xref linkend="external-projects">
  36    has information about finding them. In addition other languages can
  37    be defined by users; the basics of developing a new procedural
  38    language are covered in <xref linkend="plhandler">.
  39   </para>
  40
  41   <sect1 id="xplang-install">
  42    <title>Installing Procedural Languages</title>
  43
  44    <para>
  45     A procedural language must be <quote>installed</quote> into each
  46     database where it is to be used.  But procedural languages installed in
  47     the database <literal>template1</> are automatically available in all
  48     subsequently created databases, since their entries in
  49     <literal>template1</> will be copied by <command>CREATE DATABASE</>.
  50     So the database administrator can
  51     decide which languages are available in which databases and can make
  52     some languages available by default if he chooses.
  53    </para>
  54
  55    <para>
  56     For the languages supplied with the standard distribution, it is
  57     only necessary to execute <command>CREATE LANGUAGE</>
  58     <replaceable>language_name</> to install the language into the
  59     current database.  Alternatively, the program <xref
  60     linkend="app-createlang"> can be used to do this from the shell
  61     command line.  For example, to install the language
  62     <application>PL/pgSQL</application> into the database
  63     <literal>template1</>, use:
  64 <programlisting>
  65 createlang plpgsql template1
  66 </programlisting>
  67     The manual procedure described below is only recommended for
  68     installing custom languages that <command>CREATE LANGUAGE</command>
  69     does not know about.
  70    </para>
  71
  72    <procedure>
  73     <title>
  74      Manual Procedural Language Installation
  75     </title>
  76
  77     <para>
  78      A procedural language is installed in a database in four steps,
  79      which must be carried out by a database superuser.  (For languages
  80      known to <command>CREATE LANGUAGE</>, the second and third steps
  81      can be omitted, because they will be carried out automatically
  82      if needed.)
  83     </para>
  84
  85     <step performance="required" id="xplang-install-cr1">
  86      <para>
  87       The shared object for the language handler must be compiled and
  88       installed into an appropriate library directory.  This works in the same
  89       way as building and installing modules with regular user-defined C
  90       functions does; see <xref linkend="dfunc">.  Often, the language
  91       handler will depend on an external library that provides the actual
  92       programming language engine; if so, that must be installed as well.
  93      </para>
  94     </step>
  95
  96     <step performance="required" id="xplang-install-cr2">
  97      <para>
  98       The handler must be declared with the command
  99 <synopsis>
 100 CREATE FUNCTION <replaceable>handler_function_name</replaceable>()
 101     RETURNS language_handler
 102     AS '<replaceable>path-to-shared-object</replaceable>'
 103     LANGUAGE C;
 104 </synopsis>
 105       The special return type of <type>language_handler</type> tells
 106       the database system that this function does not return one of
 107       the defined <acronym>SQL</acronym> data types and is not directly usable
 108       in <acronym>SQL</acronym> statements.
 109      </para>
 110     </step>
 111
 112     <step performance="optional" id="xplang-install-cr3">
 113      <para>
 114       Optionally, the language handler can provide a <quote>validator</>
 115       function that checks a function definition for correctness without
 116       actually executing it.  The validator function is called by
 117       <command>CREATE FUNCTION</> if it exists.  If a validator function
 118       is provided by the handler, declare it with a command like
 119 <synopsis>
 120 CREATE FUNCTION <replaceable>validator_function_name</replaceable>(oid)
 121     RETURNS void
 122     AS '<replaceable>path-to-shared-object</replaceable>'
 123     LANGUAGE C;
 124 </synopsis>
 125      </para>
 126     </step>
 127
 128     <step performance="required" id="xplang-install-cr4">
 129      <para>
 130       The PL must be declared with the command
 131 <synopsis>
 132 CREATE <optional>TRUSTED</optional> <optional>PROCEDURAL</optional> LANGUAGE <replaceable>language-name</replaceable>
 133     HANDLER <replaceable>handler_function_name</replaceable>
 134     <optional>VALIDATOR <replaceable>validator_function_name</replaceable></optional> ;
 135 </synopsis>
 136       The optional key word <literal>TRUSTED</literal> specifies that
 137       ordinary database users that have no superuser privileges should
 138       be allowed to use this language to create functions and trigger
 139       procedures. Since PL functions are executed inside the database
 140       server, the <literal>TRUSTED</literal> flag should only be given
 141       for languages that do not allow access to database server
 142       internals or the file system. The languages
 143       <application>PL/pgSQL</application>,
 144       <application>PL/Tcl</application>, and
 145       <application>PL/Perl</application>
 146       are considered trusted; the languages
 147       <application>PL/TclU</application>,
 148       <application>PL/PerlU</application>, and
 149       <application>PL/PythonU</application>
 150       are designed to provide unlimited functionality and should
 151       <emphasis>not</emphasis> be marked trusted.
 152      </para>
 153     </step>
 154    </procedure>
 155
 156    <para>
 157     <xref linkend="xplang-install-example"> shows how the manual
 158     installation procedure would work with the language
 159     <application>PL/pgSQL</application>.
 160    </para>
 161
 162    <example id="xplang-install-example">
 163     <title>Manual Installation of <application>PL/pgSQL</application></title>
 164
 165      <para>
 166       The following command tells the database server where to find the
 167       shared object for the <application>PL/pgSQL</application> language's call handler function.
 168
 169 <programlisting>
 170 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
 171     '$libdir/plpgsql' LANGUAGE C;
 172 </programlisting>
 173      </para>
 174
 175      <para>
 176       <application>PL/pgSQL</application> has a validator function,
 177       so we declare that too:
 178
 179 <programlisting>
 180 CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
 181     '$libdir/plpgsql' LANGUAGE C;
 182 </programlisting>
 183      </para>
 184
 185      <para>
 186       The command:
 187 <programlisting>
 188 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
 189     HANDLER plpgsql_call_handler
 190     VALIDATOR plpgsql_validator;
 191 </programlisting>
 192       then defines that the previously declared functions
 193       should be invoked for functions and trigger procedures where the
 194       language attribute is <literal>plpgsql</literal>.
 195      </para>
 196   </example>
 197
 198    <para>
 199     In a default <productname>PostgreSQL</productname> installation,
 200     the handler for the <application>PL/pgSQL</application> language
 201     is built and installed into the <quote>library</quote>
 202     directory. If <application>Tcl</> support is configured in, the handlers
 203     for <application>PL/Tcl</> and <application>PL/TclU</> are also built and
 204     installed in the same location.  Likewise, the <application>PL/Perl</> and
 205     <application>PL/PerlU</> handlers are built and installed if Perl support
 206     is configured, and the <application>PL/PythonU</> handler is
 207     installed if Python support is configured.
 208    </para>
 209
 210   </sect1>
 211
 212 </chapter>