1<!-- doc/src/sgml/plhandler.sgml -->
2
3 <chapter id="plhandler">
4   <title>Writing a Procedural Language Handler</title>
5
6   <indexterm zone="plhandler">
7    <primary>procedural language</primary>
8    <secondary>handler for</secondary>
9   </indexterm>
10
11   <para>
12    All calls to functions that are written in a language other than
13    the current <quote>version 1</quote> interface for compiled
14    languages (this includes functions in user-defined procedural languages
15    and functions written in SQL) go through a <firstterm>call handler</firstterm>
16    function for the specific language.  It is the responsibility of
17    the call handler to execute the function in a meaningful way, such
18    as by interpreting the supplied source text.  This chapter outlines
19    how a new procedural language's call handler can be written.
20   </para>
21
22   <para>
23    The call handler for a procedural language is a
24    <quote>normal</quote> function that must be written in a compiled
25    language such as C, using the version-1 interface, and registered
26    with <productname>PostgreSQL</productname> as taking no arguments
27    and returning the type <type>language_handler</type>.  This
28    special pseudo-type identifies the function as a call handler and
29    prevents it from being called directly in SQL commands.
30    For more details on C language calling conventions and dynamic loading,
31    see <xref linkend="xfunc-c"/>.
32   </para>
33
34   <para>
35    The call handler is called in the same way as any other function:
36    It receives a pointer to a
37    <structname>FunctionCallInfoBaseData</structname> <type>struct</type> containing
38    argument values and information about the called function, and it
39    is expected to return a <type>Datum</type> result (and possibly
40    set the <structfield>isnull</structfield> field of the
41    <structname>FunctionCallInfoBaseData</structname> structure, if it wishes
42    to return an SQL null result).  The difference between a call
43    handler and an ordinary callee function is that the
44    <structfield>flinfo-&gt;fn_oid</structfield> field of the
45    <structname>FunctionCallInfoBaseData</structname> structure will contain
46    the OID of the actual function to be called, not of the call
47    handler itself.  The call handler must use this field to determine
48    which function to execute.  Also, the passed argument list has
49    been set up according to the declaration of the target function,
50    not of the call handler.
51   </para>
52
53   <para>
54    It's up to the call handler to fetch the entry of the function from the
55    <classname>pg_proc</classname> system catalog and to analyze the argument
56    and return types of the called function. The <literal>AS</literal> clause from the
57    <command>CREATE FUNCTION</command> command for the function will be found
58    in the <literal>prosrc</literal> column of the
59    <classname>pg_proc</classname> row. This is commonly source
60    text in the procedural language, but in theory it could be something else,
61    such as a path name to a file, or anything else that tells the call handler
62    what to do in detail.
63   </para>
64
65   <para>
66    Often, the same function is called many times per SQL statement.
67    A call handler can avoid repeated lookups of information about the
68    called function by using the
69    <structfield>flinfo-&gt;fn_extra</structfield> field.  This will
70    initially be <symbol>NULL</symbol>, but can be set by the call handler to point at
71    information about the called function.  On subsequent calls, if
72    <structfield>flinfo-&gt;fn_extra</structfield> is already non-<symbol>NULL</symbol>
73    then it can be used and the information lookup step skipped.  The
74    call handler must make sure that
75    <structfield>flinfo-&gt;fn_extra</structfield> is made to point at
76    memory that will live at least until the end of the current query,
77    since an <structname>FmgrInfo</structname> data structure could be
78    kept that long.  One way to do this is to allocate the extra data
79    in the memory context specified by
80    <structfield>flinfo-&gt;fn_mcxt</structfield>; such data will
81    normally have the same lifespan as the
82    <structname>FmgrInfo</structname> itself.  But the handler could
83    also choose to use a longer-lived memory context so that it can cache
84    function definition information across queries.
85   </para>
86
87   <para>
88    When a procedural-language function is invoked as a trigger, no arguments
89    are passed in the usual way, but the
90    <structname>FunctionCallInfoBaseData</structname>'s
91    <structfield>context</structfield> field points at a
92    <structname>TriggerData</structname> structure, rather than being <symbol>NULL</symbol>
93    as it is in a plain function call.  A language handler should
94    provide mechanisms for procedural-language functions to get at the trigger
95    information.
96   </para>
97
98   <para>
99    This is a template for a procedural-language handler written in C:
100<programlisting>
101#include "postgres.h"
102#include "executor/spi.h"
103#include "commands/trigger.h"
104#include "fmgr.h"
105#include "access/heapam.h"
106#include "utils/syscache.h"
107#include "catalog/pg_proc.h"
108#include "catalog/pg_type.h"
109
110PG_MODULE_MAGIC;
111
112PG_FUNCTION_INFO_V1(plsample_call_handler);
113
114Datum
115plsample_call_handler(PG_FUNCTION_ARGS)
116{
117    Datum          retval;
118
119    if (CALLED_AS_TRIGGER(fcinfo))
120    {
121        /*
122         * Called as a trigger function
123         */
124        TriggerData    *trigdata = (TriggerData *) fcinfo-&gt;context;
125
126        retval = ...
127    }
128    else
129    {
130        /*
131         * Called as a function
132         */
133
134        retval = ...
135    }
136
137    return retval;
138}
139</programlisting>
140    Only a few thousand lines of code have to be added instead of the
141    dots to complete the call handler.
142   </para>
143
144   <para>
145    After having compiled the handler function into a loadable module
146    (see <xref linkend="dfunc"/>), the following commands then
147    register the sample procedural language:
148<programlisting>
149CREATE FUNCTION plsample_call_handler() RETURNS language_handler
150    AS '<replaceable>filename</replaceable>'
151    LANGUAGE C;
152CREATE LANGUAGE plsample
153    HANDLER plsample_call_handler;
154</programlisting>
155   </para>
156
157   <para>
158    Although providing a call handler is sufficient to create a minimal
159    procedural language, there are two other functions that can optionally
160    be provided to make the language more convenient to use.  These
161    are a <firstterm>validator</firstterm> and an
162    <firstterm>inline handler</firstterm>.  A validator can be provided
163    to allow language-specific checking to be done during
164    <xref linkend="sql-createfunction"/>.
165    An inline handler can be provided to allow the language to support
166    anonymous code blocks executed via the <xref linkend="sql-do"/> command.
167   </para>
168
169   <para>
170    If a validator is provided by a procedural language, it
171    must be declared as a function taking a single parameter of type
172    <type>oid</type>.  The validator's result is ignored, so it is customarily
173    declared to return <type>void</type>.  The validator will be called at
174    the end of a <command>CREATE FUNCTION</command> command that has created
175    or updated a function written in the procedural language.
176    The passed-in OID is the OID of the function's <classname>pg_proc</classname>
177    row.  The validator must fetch this row in the usual way, and do
178    whatever checking is appropriate.
179    First, call <function>CheckFunctionValidatorAccess()</function> to diagnose
180    explicit calls to the validator that the user could not achieve through
181    <command>CREATE FUNCTION</command>.  Typical checks then include verifying
182    that the function's argument and result types are supported by the
183    language, and that the function's body is syntactically correct
184    in the language.  If the validator finds the function to be okay,
185    it should just return.  If it finds an error, it should report that
186    via the normal <function>ereport()</function> error reporting mechanism.
187    Throwing an error will force a transaction rollback and thus prevent
188    the incorrect function definition from being committed.
189   </para>
190
191   <para>
192    Validator functions should typically honor the <xref
193    linkend="guc-check-function-bodies"/> parameter: if it is turned off then
194    any expensive or context-sensitive checking should be skipped.  If the
195    language provides for code execution at compilation time, the validator
196    must suppress checks that would induce such execution.  In particular,
197    this parameter is turned off by <application>pg_dump</application> so that it can
198    load procedural language functions without worrying about side effects or
199    dependencies of the function bodies on other database objects.
200    (Because of this requirement, the call handler should avoid
201    assuming that the validator has fully checked the function.  The point
202    of having a validator is not to let the call handler omit checks, but
203    to notify the user immediately if there are obvious errors in a
204    <command>CREATE FUNCTION</command> command.)
205    While the choice of exactly what to check is mostly left to the
206    discretion of the validator function, note that the core
207    <command>CREATE FUNCTION</command> code only executes <literal>SET</literal> clauses
208    attached to a function when <varname>check_function_bodies</varname> is on.
209    Therefore, checks whose results might be affected by GUC parameters
210    definitely should be skipped when <varname>check_function_bodies</varname> is
211    off, to avoid false failures when reloading a dump.
212   </para>
213
214   <para>
215    If an inline handler is provided by a procedural language, it
216    must be declared as a function taking a single parameter of type
217    <type>internal</type>.  The inline handler's result is ignored, so it is
218    customarily declared to return <type>void</type>.  The inline handler
219    will be called when a <command>DO</command> statement is executed specifying
220    the procedural language.  The parameter actually passed is a pointer
221    to an <structname>InlineCodeBlock</structname> struct, which contains information
222    about the <command>DO</command> statement's parameters, in particular the
223    text of the anonymous code block to be executed.  The inline handler
224    should execute this code and return.
225   </para>
226
227   <para>
228    It's recommended that you wrap all these function declarations,
229    as well as the <command>CREATE LANGUAGE</command> command itself, into
230    an <firstterm>extension</firstterm> so that a simple <command>CREATE EXTENSION</command>
231    command is sufficient to install the language.  See
232    <xref linkend="extend-extensions"/> for information about writing
233    extensions.
234   </para>
235
236   <para>
237    The procedural languages included in the standard distribution
238    are good references when trying to write your own language handler.
239    Look into the <filename>src/pl</filename> subdirectory of the source tree.
240    The <xref linkend="sql-createlanguage"/>
241    reference page also has some useful details.
242   </para>
243
244 </chapter>
245