| 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> |