<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>42.1. Installing Procedural Languages</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="xplang.html" title="Chapter 42. Procedural Languages" /><link rel="next" href="plpgsql.html" title="Chapter 43. PL/pgSQL - SQL Procedural Language" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">42.1. Installing Procedural Languages</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xplang.html" title="Chapter 42. Procedural Languages">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="xplang.html" title="Chapter 42. Procedural Languages">Up</a></td><th width="60%" align="center">Chapter 42. Procedural Languages</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql.html" title="Chapter 43. PL/pgSQL - SQL Procedural Language">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="XPLANG-INSTALL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">42.1. Installing Procedural Languages</h2></div></div></div><p>
A procedural language must be <span class="quote">“<span class="quote">installed</span>”</span> into each
database where it is to be used. But procedural languages installed in
the database <code class="literal">template1</code> are automatically available in all
subsequently created databases, since their entries in
<code class="literal">template1</code> will be copied by <code class="command">CREATE DATABASE</code>.
So the database administrator can
decide which languages are available in which databases and can make
some languages available by default if desired.
</p><p>
For the languages supplied with the standard distribution, it is
only necessary to execute <code class="command">CREATE EXTENSION</code>
<em class="replaceable"><code>language_name</code></em> to install the language into the
current database.
The manual procedure described below is only recommended for
installing languages that have not been packaged as extensions.
</p><div class="procedure" id="id-1.8.7.5.4"><p class="title"><strong>
Manual Procedural Language Installation
</strong></p><p>
A procedural language is installed in a database in five steps,
which must be carried out by a database superuser. In most cases
the required SQL commands should be packaged as the installation script
of an <span class="quote">“<span class="quote">extension</span>”</span>, so that <code class="command">CREATE EXTENSION</code> can be
used to execute them.
</p><ol class="procedure" type="1"><li class="step" id="XPLANG-INSTALL-CR1"><p>
The shared object for the language handler must be compiled and
installed into an appropriate library directory. This works in the same
way as building and installing modules with regular user-defined C
functions does; see <a class="xref" href="xfunc-c.html#DFUNC" title="38.10.5. Compiling and Linking Dynamically-loaded Functions">Section 38.10.5</a>. Often, the language
handler will depend on an external library that provides the actual
programming language engine; if so, that must be installed as well.
</p></li><li class="step" id="XPLANG-INSTALL-CR2"><p>
The handler must be declared with the command
</p><pre class="synopsis">
CREATE FUNCTION <em class="replaceable"><code>handler_function_name</code></em>()
RETURNS language_handler
AS '<em class="replaceable"><code>path-to-shared-object</code></em>'
LANGUAGE C;
</pre><p>
The special return type of <code class="type">language_handler</code> tells
the database system that this function does not return one of
the defined <acronym class="acronym">SQL</acronym> data types and is not directly usable
in <acronym class="acronym">SQL</acronym> statements.
</p></li><li class="step" id="XPLANG-INSTALL-CR3"><p>
Optionally, the language handler can provide an <span class="quote">“<span class="quote">inline</span>”</span>
handler function that executes anonymous code blocks
(<a class="xref" href="sql-do.html" title="DO"><span class="refentrytitle">DO</span></a> commands)
written in this language. If an inline handler function
is provided by the language, declare it with a command like
</p><pre class="synopsis">
CREATE FUNCTION <em class="replaceable"><code>inline_function_name</code></em>(internal)
RETURNS void
AS '<em class="replaceable"><code>path-to-shared-object</code></em>'
LANGUAGE C;
</pre><p>
</p></li><li class="step" id="XPLANG-INSTALL-CR4"><p>
Optionally, the language handler can provide a <span class="quote">“<span class="quote">validator</span>”</span>
function that checks a function definition for correctness without
actually executing it. The validator function is called by
<code class="command">CREATE FUNCTION</code> if it exists. If a validator function
is provided by the language, declare it with a command like
</p><pre class="synopsis">
CREATE FUNCTION <em class="replaceable"><code>validator_function_name</code></em>(oid)
RETURNS void
AS '<em class="replaceable"><code>path-to-shared-object</code></em>'
LANGUAGE C STRICT;
</pre><p>
</p></li><li class="step" id="XPLANG-INSTALL-CR5"><p>
Finally, the PL must be declared with the command
</p><pre class="synopsis">
CREATE [<span class="optional">TRUSTED</span>] [<span class="optional">PROCEDURAL</span>] LANGUAGE <em class="replaceable"><code>language-name</code></em>
HANDLER <em class="replaceable"><code>handler_function_name</code></em>
[<span class="optional">INLINE <em class="replaceable"><code>inline_function_name</code></em></span>]
[<span class="optional">VALIDATOR <em class="replaceable"><code>validator_function_name</code></em></span>] ;
</pre><p>
The optional key word <code class="literal">TRUSTED</code> specifies that
the language does not grant access to data that the user would
not otherwise have. Trusted languages are designed for ordinary
database users (those without superuser privilege) and allows them
to safely create functions and
procedures. Since PL functions are executed inside the database
server, the <code class="literal">TRUSTED</code> flag should only be given
for languages that do not allow access to database server
internals or the file system. The languages
<span class="application">PL/pgSQL</span>,
<span class="application">PL/Tcl</span>, and
<span class="application">PL/Perl</span>
are considered trusted; the languages
<span class="application">PL/TclU</span>,
<span class="application">PL/PerlU</span>, and
<span class="application">PL/PythonU</span>
are designed to provide unlimited functionality and should
<span class="emphasis"><em>not</em></span> be marked trusted.
</p></li></ol></div><p>
<a class="xref" href="xplang-install.html#XPLANG-INSTALL-EXAMPLE" title="Example 42.1. Manual Installation of PL/Perl">Example 42.1</a> shows how the manual
installation procedure would work with the language
<span class="application">PL/Perl</span>.
</p><div class="example" id="XPLANG-INSTALL-EXAMPLE"><p class="title"><strong>Example 42.1. Manual Installation of <span class="application">PL/Perl</span></strong></p><div class="example-contents"><p>
The following command tells the database server where to find the
shared object for the <span class="application">PL/Perl</span> language's call
handler function:
</p><pre class="programlisting">
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
'$libdir/plperl' LANGUAGE C;
</pre><p>
</p><p>
<span class="application">PL/Perl</span> has an inline handler function
and a validator function, so we declare those too:
</p><pre class="programlisting">
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
'$libdir/plperl' LANGUAGE C;
CREATE FUNCTION plperl_validator(oid) RETURNS void AS
'$libdir/plperl' LANGUAGE C STRICT;
</pre><p>
</p><p>
The command:
</p><pre class="programlisting">
CREATE TRUSTED PROCEDURAL LANGUAGE plperl
HANDLER plperl_call_handler
INLINE plperl_inline_handler
VALIDATOR plperl_validator;
</pre><p>
then defines that the previously declared functions
should be invoked for functions and procedures where the
language attribute is <code class="literal">plperl</code>.
</p></div></div><br class="example-break" /><p>
In a default <span class="productname">PostgreSQL</span> installation,
the handler for the <span class="application">PL/pgSQL</span> language
is built and installed into the <span class="quote">“<span class="quote">library</span>”</span>
directory; furthermore, the <span class="application">PL/pgSQL</span> language
itself is installed in all databases.
If <span class="application">Tcl</span> support is configured in, the handlers for
<span class="application">PL/Tcl</span> and <span class="application">PL/TclU</span> are built and installed
in the library directory, but the language itself is not installed in any
database by default.
Likewise, the <span class="application">PL/Perl</span> and <span class="application">PL/PerlU</span>
handlers are built and installed if Perl support is configured, and the
<span class="application">PL/PythonU</span> handler is installed if Python support is
configured, but these languages are not installed by default.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xplang.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="xplang.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 42. Procedural Languages </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 43. <span class="application">PL/pgSQL</span> - <acronym class="acronym">SQL</acronym> Procedural Language</td></tr></table></div></body></html>
