1 <!-- doc/src/sgml/xplang.sgml -->
4 <title>Procedural Languages</title>
6 <indexterm zone="xplang">
7 <primary>procedural language</primary>
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.
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">.
41 <sect1 id="xplang-install">
42 <title>Installing Procedural Languages</title>
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 desired.
56 For the languages supplied with the standard distribution, it is
57 only necessary to execute <command>CREATE EXTENSION</>
58 <replaceable>language_name</> to install the language into the
60 The manual procedure described below is only recommended for
61 installing languages that have not been packaged as extensions.
66 Manual Procedural Language Installation
70 A procedural language is installed in a database in five steps,
71 which must be carried out by a database superuser. In most cases
72 the required SQL commands should be packaged as the installation script
73 of an <quote>extension</>, so that <command>CREATE EXTENSION</> can be
77 <step performance="required" id="xplang-install-cr1">
79 The shared object for the language handler must be compiled and
80 installed into an appropriate library directory. This works in the same
81 way as building and installing modules with regular user-defined C
82 functions does; see <xref linkend="dfunc">. Often, the language
83 handler will depend on an external library that provides the actual
84 programming language engine; if so, that must be installed as well.
88 <step performance="required" id="xplang-install-cr2">
90 The handler must be declared with the command
92 CREATE FUNCTION <replaceable>handler_function_name</replaceable>()
93 RETURNS language_handler
94 AS '<replaceable>path-to-shared-object</replaceable>'
97 The special return type of <type>language_handler</type> tells
98 the database system that this function does not return one of
99 the defined <acronym>SQL</acronym> data types and is not directly usable
100 in <acronym>SQL</acronym> statements.
104 <step performance="optional" id="xplang-install-cr3">
106 Optionally, the language handler can provide an <quote>inline</>
107 handler function that executes anonymous code blocks
108 (<xref linkend="sql-do"> commands)
109 written in this language. If an inline handler function
110 is provided by the language, declare it with a command like
112 CREATE FUNCTION <replaceable>inline_function_name</replaceable>(internal)
114 AS '<replaceable>path-to-shared-object</replaceable>'
120 <step performance="optional" id="xplang-install-cr4">
122 Optionally, the language handler can provide a <quote>validator</>
123 function that checks a function definition for correctness without
124 actually executing it. The validator function is called by
125 <command>CREATE FUNCTION</> if it exists. If a validator function
126 is provided by the language, declare it with a command like
128 CREATE FUNCTION <replaceable>validator_function_name</replaceable>(oid)
130 AS '<replaceable>path-to-shared-object</replaceable>'
136 <step performance="required" id="xplang-install-cr5">
138 Finally, the PL must be declared with the command
140 CREATE <optional>TRUSTED</optional> <optional>PROCEDURAL</optional> LANGUAGE <replaceable>language-name</replaceable>
141 HANDLER <replaceable>handler_function_name</replaceable>
142 <optional>INLINE <replaceable>inline_function_name</replaceable></optional>
143 <optional>VALIDATOR <replaceable>validator_function_name</replaceable></optional> ;
145 The optional key word <literal>TRUSTED</literal> specifies that
146 the language does not grant access to data that the user would
147 not otherwise have. Trusted languages are designed for ordinary
148 database users (those without superuser privilege) and allows them
149 to safely create functions and trigger
150 procedures. Since PL functions are executed inside the database
151 server, the <literal>TRUSTED</literal> flag should only be given
152 for languages that do not allow access to database server
153 internals or the file system. The languages
154 <application>PL/pgSQL</application>,
155 <application>PL/Tcl</application>, and
156 <application>PL/Perl</application>
157 are considered trusted; the languages
158 <application>PL/TclU</application>,
159 <application>PL/PerlU</application>, and
160 <application>PL/PythonU</application>
161 are designed to provide unlimited functionality and should
162 <emphasis>not</emphasis> be marked trusted.
168 <xref linkend="xplang-install-example"> shows how the manual
169 installation procedure would work with the language
170 <application>PL/Perl</application>.
173 <example id="xplang-install-example">
174 <title>Manual Installation of <application>PL/Perl</application></title>
177 The following command tells the database server where to find the
178 shared object for the <application>PL/Perl</application> language's call
182 CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
183 '$libdir/plperl' LANGUAGE C;
188 <application>PL/Perl</application> has an inline handler function
189 and a validator function, so we declare those too:
192 CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
193 '$libdir/plperl' LANGUAGE C;
195 CREATE FUNCTION plperl_validator(oid) RETURNS void AS
196 '$libdir/plperl' LANGUAGE C STRICT;
203 CREATE TRUSTED PROCEDURAL LANGUAGE plperl
204 HANDLER plperl_call_handler
205 INLINE plperl_inline_handler
206 VALIDATOR plperl_validator;
208 then defines that the previously declared functions
209 should be invoked for functions and trigger procedures where the
210 language attribute is <literal>plperl</literal>.
215 In a default <productname>PostgreSQL</productname> installation,
216 the handler for the <application>PL/pgSQL</application> language
217 is built and installed into the <quote>library</quote>
218 directory; furthermore, the <application>PL/pgSQL</application> language
219 itself is installed in all databases.
220 If <application>Tcl</> support is configured in, the handlers for
221 <application>PL/Tcl</> and <application>PL/TclU</> are built and installed
222 in the library directory, but the language itself is not installed in any
224 Likewise, the <application>PL/Perl</> and <application>PL/PerlU</>
225 handlers are built and installed if Perl support is configured, and the
226 <application>PL/PythonU</> handler is installed if Python support is
227 configured, but these languages are not installed by default.