2 $Header: /cvsroot/pgsql/doc/src/sgml/xplang.sgml,v 1.19 2002/09/21 18:32:54 petere Exp $
6 <title id="xplang-title">Procedural Languages</title>
8 <sect1 id="xplang-intro">
9 <title>Introduction</title>
12 <productname>PostgreSQL</productname> allows users to add new
13 programming languages to be available for writing functions and
14 procedures. These are called <firstterm>procedural
15 languages</firstterm> (PL). In the case of a function or trigger
16 procedure written in a procedural language, the database server has
17 no built-in knowledge about how to interpret the function's source
18 text. Instead, the task is passed to a special handler that knows
19 the details of the language. The handler could either do all the
20 work of parsing, syntax analysis, execution, etc. itself, or it
21 could serve as <quote>glue</quote> between
22 <productname>PostgreSQL</productname> and an existing implementation
23 of a programming language. The handler itself is a special
24 programming language function compiled into a shared object and
29 Writing a handler for a new procedural language is described in
30 <xref linkend="xfunc-plhandler">. Several procedural languages are
31 available in the standard <productname>PostgreSQL</productname>
32 distribution, which can serve as examples.
36 <sect1 id="xplang-install">
37 <title>Installing Procedural Languages</title>
40 A procedural language must be <quote>installed</quote> into each
41 database where it is to be used. But procedural languages installed in
42 the template1 database are automatically available in all
43 subsequently created databases. So the database administrator can
44 decide which languages are available in which databases, and can make
45 some languages available by default if he chooses.
49 For the languages supplied with the standard distribution, the
50 shell script <filename>createlang</filename> may be used instead
51 of carrying out the details by hand. For example, to install <application>PL/pgSQL</application>
52 into the template1 database, use
54 createlang plpgsql template1
56 The manual procedure described below is only recommended for
57 installing custom languages that <filename>createlang</filename>
63 Manual Procedural Language Installation
67 A procedural language is installed in the database in three
68 steps, which must be carried out by a database superuser.
71 <step performance="required">
73 The shared object for the language handler must be compiled and
74 installed into an appropriate library directory. This works in the same
75 way as building and installing modules with regular user-defined C
76 functions does; see <xref linkend="dfunc">.
80 <step performance="required" id="xplang-install-cr1">
82 The handler must be declared with the command
84 CREATE FUNCTION <replaceable>handler_function_name</replaceable> ()
85 RETURNS LANGUAGE_HANDLER AS
86 '<replaceable>path-to-shared-object</replaceable>' LANGUAGE C;
88 The special return type of <type>LANGUAGE_HANDLER</type> tells
89 the database that this function does not return one of
90 the defined <acronym>SQL</acronym> data types and is not directly usable
91 in <acronym>SQL</acronym> statements.
95 <step performance="required" id="xplang-install-cr2">
97 The PL must be declared with the command
99 CREATE <optional>TRUSTED</optional> <optional>PROCEDURAL</optional> LANGUAGE <replaceable>language-name</replaceable>
100 HANDLER <replaceable>handler_function_name</replaceable>;
102 The optional key word <literal>TRUSTED</literal> tells whether
103 ordinary database users that have no superuser privileges should
104 be allowed to use this language to create functions and trigger
105 procedures. Since PL functions are executed inside the database
106 server, the <literal>TRUSTED</literal> flag should only be given
107 for languages that do not allow access to database server
108 internals or the file system. The languages
109 <application>PL/pgSQL</application>,
110 <application>PL/Tcl</application>,
111 <application>PL/Perl</application>, and
112 <application>PL/Python</application> are known to be trusted;
113 the languages <application>PL/TclU</application> and
114 <application>PL/PerlU</application> are designed to provide
115 unlimited functionality should <emphasis>not</emphasis> be
122 In a default <productname>PostgreSQL</productname> installation,
123 the handler for the <application>PL/pgSQL</application> language
124 is built and installed into the <quote>library</quote>
125 directory. If <application>Tcl/Tk</> support is configured in, the handlers for
126 <application>PL/Tcl</> and <application>PL/TclU</> are also built and installed in the same
127 location. Likewise, the <application>PL/Perl</> and <application>PL/PerlU</> handlers are built
128 and installed if Perl support is configured, and <application>PL/Python</> is
129 installed if Python support is configured. The
130 <filename>createlang</filename> script automates <xref
131 linkend="xplang-install-cr1"> and <xref
132 linkend="xplang-install-cr2"> described above.
136 <title>Manual Installation of <application>PL/pgSQL</application></title>
139 The following command tells the database server where to find the
140 shared object for the <application>PL/pgSQL</application> language's call handler function.
143 CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
144 '$libdir/plpgsql' LANGUAGE C;
151 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
152 HANDLER plpgsql_call_handler;
154 then defines that the previously declared call handler function
155 should be invoked for functions and trigger procedures where the
156 language attribute is <literal>plpgsql</literal>.
164 <!-- Keep this comment at the end of the file
169 sgml-minimize-attributes:nil
170 sgml-always-quote-attributes:t
173 sgml-parent-document:nil
174 sgml-default-dtd-file:"./reference.ced"
175 sgml-exposed-tags:nil
176 sgml-local-catalogs:("/usr/lib/sgml/catalog")
177 sgml-local-ecat-files:nil