doc/src/sgml/xplang.sgml

   1 <!-- doc/src/sgml/xplang.sgml -->
   2
   3  <chapter id="xplang">
   4   <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</acronym>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</literal> are automatically available in all
  48     subsequently created databases, since their entries in
  49     <literal>template1</literal> will be copied by <command>CREATE DATABASE</command>.
  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 desired.
  53    </para>
  54
  55    <para>
  56     For the languages supplied with the standard distribution, it is
  57     only necessary to execute <command>CREATE EXTENSION</command>
  58     <replaceable>language_name</replaceable> to install the language into the
  59     current database.
  60     The manual procedure described below is only recommended for
  61     installing languages that have not been packaged as extensions.
  62    </para>
  63
  64    <procedure>
  65     <title>Manual Procedural Language Installation</title>
  66
  67     <para>
  68      A procedural language is installed in a database in five steps,
  69      which must be carried out by a database superuser.  In most cases
  70      the required SQL commands should be packaged as the installation script
  71      of an <quote>extension</quote>, so that <command>CREATE EXTENSION</command> can be
  72      used to execute them.
  73     </para>
  74
  75     <step performance="required" id="xplang-install-cr1">
  76      <para>
  77       The shared object for the language handler must be compiled and
  78       installed into an appropriate library directory.  This works in the same
  79       way as building and installing modules with regular user-defined C
  80       functions does; see <xref linkend="dfunc"/>.  Often, the language
  81       handler will depend on an external library that provides the actual
  82       programming language engine; if so, that must be installed as well.
  83      </para>
  84     </step>
  85
  86     <step performance="required" id="xplang-install-cr2">
  87      <para>
  88       The handler must be declared with the command
  89 <synopsis>
  90 CREATE FUNCTION <replaceable>handler_function_name</replaceable>()
  91     RETURNS language_handler
  92     AS '<replaceable>path-to-shared-object</replaceable>'
  93     LANGUAGE C;
  94 </synopsis>
  95       The special return type of <type>language_handler</type> tells
  96       the database system that this function does not return one of
  97       the defined <acronym>SQL</acronym> data types and is not directly usable
  98       in <acronym>SQL</acronym> statements.
  99      </para>
 100     </step>
 101
 102     <step performance="optional" id="xplang-install-cr3">
 103      <para>
 104       Optionally, the language handler can provide an <quote>inline</quote>
 105       handler function that executes anonymous code blocks
 106       (<link linkend="sql-do"><command>DO</command></link> commands)
 107       written in this language.  If an inline handler function
 108       is provided by the language, declare it with a command like
 109 <synopsis>
 110 CREATE FUNCTION <replaceable>inline_function_name</replaceable>(internal)
 111     RETURNS void
 112     AS '<replaceable>path-to-shared-object</replaceable>'
 113     LANGUAGE C;
 114 </synopsis>
 115      </para>
 116     </step>
 117
 118     <step performance="optional" id="xplang-install-cr4">
 119      <para>
 120       Optionally, the language handler can provide a <quote>validator</quote>
 121       function that checks a function definition for correctness without
 122       actually executing it.  The validator function is called by
 123       <command>CREATE FUNCTION</command> if it exists.  If a validator function
 124       is provided by the language, declare it with a command like
 125 <synopsis>
 126 CREATE FUNCTION <replaceable>validator_function_name</replaceable>(oid)
 127     RETURNS void
 128     AS '<replaceable>path-to-shared-object</replaceable>'
 129     LANGUAGE C STRICT;
 130 </synopsis>
 131      </para>
 132     </step>
 133
 134     <step performance="required" id="xplang-install-cr5">
 135      <para>
 136       Finally, the PL must be declared with the command
 137 <synopsis>
 138 CREATE <optional>TRUSTED</optional> LANGUAGE <replaceable>language_name</replaceable>
 139     HANDLER <replaceable>handler_function_name</replaceable>
 140     <optional>INLINE <replaceable>inline_function_name</replaceable></optional>
 141     <optional>VALIDATOR <replaceable>validator_function_name</replaceable></optional> ;
 142 </synopsis>
 143       The optional key word <literal>TRUSTED</literal> specifies that
 144       the language does not grant access to data that the user would
 145       not otherwise have.  Trusted languages are designed for ordinary
 146       database users (those without superuser privilege) and allows them
 147       to safely create functions and
 148       procedures. Since PL functions are executed inside the database
 149       server, the <literal>TRUSTED</literal> flag should only be given
 150       for languages that do not allow access to database server
 151       internals or the file system. The languages
 152       <application>PL/pgSQL</application>,
 153       <application>PL/Tcl</application>, and
 154       <application>PL/Perl</application>
 155       are considered trusted; the languages
 156       <application>PL/TclU</application>,
 157       <application>PL/PerlU</application>, and
 158       <application>PL/PythonU</application>
 159       are designed to provide unlimited functionality and should
 160       <emphasis>not</emphasis> be marked trusted.
 161      </para>
 162     </step>
 163    </procedure>
 164
 165    <para>
 166     <xref linkend="xplang-install-example"/> shows how the manual
 167     installation procedure would work with the language
 168     <application>PL/Perl</application>.
 169    </para>
 170
 171    <example id="xplang-install-example">
 172     <title>Manual Installation of <application>PL/Perl</application></title>
 173
 174      <para>
 175       The following command tells the database server where to find the
 176       shared object for the <application>PL/Perl</application> language's call
 177       handler function:
 178
 179 <programlisting>
 180 CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
 181     '$libdir/plperl' LANGUAGE C;
 182 </programlisting>
 183      </para>
 184
 185      <para>
 186       <application>PL/Perl</application> has an inline handler function
 187       and a validator function, so we declare those too:
 188
 189 <programlisting>
 190 CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
 191     '$libdir/plperl' LANGUAGE C STRICT;
 192
 193 CREATE FUNCTION plperl_validator(oid) RETURNS void AS
 194     '$libdir/plperl' LANGUAGE C STRICT;
 195 </programlisting>
 196      </para>
 197
 198      <para>
 199       The command:
 200 <programlisting>
 201 CREATE TRUSTED LANGUAGE plperl
 202     HANDLER plperl_call_handler
 203     INLINE plperl_inline_handler
 204     VALIDATOR plperl_validator;
 205 </programlisting>
 206       then defines that the previously declared functions
 207       should be invoked for functions and procedures where the
 208       language attribute is <literal>plperl</literal>.
 209      </para>
 210   </example>
 211
 212    <para>
 213     In a default <productname>PostgreSQL</productname> installation,
 214     the handler for the <application>PL/pgSQL</application> language
 215     is built and installed into the <quote>library</quote>
 216     directory; furthermore, the <application>PL/pgSQL</application> language
 217     itself is installed in all databases.
 218     If <application>Tcl</application> support is configured in, the handlers for
 219     <application>PL/Tcl</application> and <application>PL/TclU</application> are built and installed
 220     in the library directory, but the language itself is not installed in any
 221     database by default.
 222     Likewise, the <application>PL/Perl</application> and <application>PL/PerlU</application>
 223     handlers are built and installed if Perl support is configured, and the
 224     <application>PL/PythonU</application> handler is installed if Python support is
 225     configured, but these languages are not installed by default.
 226    </para>
 227
 228   </sect1>
 229
 230 </chapter>