Hi everyone,
Would some additional procedure language handler code examples in the
documentation be good to add? I've put some together in the attached
patch, and can log it to a future commitfest if people think it would
be a good addition.
Regards,
Mark
--
Mark Wong
2ndQuadrant - PostgreSQL Solutions for the Enterprise
https://www.2ndQuadrant.com/
diff --git a/doc/src/sgml/plhandler.sgml b/doc/src/sgml/plhandler.sgml
index e1b0af7a60..0287d424cb 100644
--- a/doc/src/sgml/plhandler.sgml
+++ b/doc/src/sgml/plhandler.sgml
@@ -241,4 +241,560 @@ CREATE LANGUAGE plsample
reference page also has some useful details.
</para>
+ <para>
+ The following subsections contain additional examples to help build a
+ complete procedural language handler.
+ </para>
+
+ <sect1 id="plhandler-minimal">
+ <title>Minimal Example</title>
+
+ <para>
+ Here is a complete minimal example that builds a procedural language
+ handler <application>PL/Sample</application> as an extension. Functions
+ can be created and called for <application>PL/Sample</application> but
+ they will not be able to perform any usefule actions.
+ </para>
+
+ <para>
+ The <filename>plsample--0.1.sql</filename> file:
+<programlisting>
+CREATE FUNCTION plsample_call_handler()
+RETURNS language_handler
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+CREATE LANGUAGE plsample
+HANDLER plsample_call_handler;
+
+COMMENT ON LANGUAGE plsample IS 'PL/Sample procedural language';
+</programlisting>
+ </para>
+
+ <para>
+ The control file <filename>plsample.control</filename> looks like this:
+<programlisting>
+comment = 'PL/Sample'
+default_version = '0.1'
+module_pathname = '$libdir/plsample'
+relocatable = false
+schema = pg_catalog
+superuser = false
+</programlisting>
+ See <xref linkend="extend-extensions"/> for more information about writing
+ control files.
+ </para>
+
+ <para>
+ The following <filename>Makefile</filename> relies on
+ <acronym>PGXS</acronym>.
+<programlisting>
+PGFILEDESC = "PL/Sample - procedural language"
+
+EXTENSION = plsample
+EXTVERSION = 0.1
+
+MODULE_big = plsample
+
+OBJS = plsample.o
+
+DATA = plsample.control plsample--0.1.sql
+
+plsample.o: plsample.c
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+</programlisting>
+ See <xref linkend="extend-pgxs"/> for more information on makefiles with
+ <acronym>PGXS</acronym>.
+ </para>
+
+ <para>
+ Here is the minimal C code in <filename>plsample.c</filename> that will
+ handle calls to this sample procedural language:
+<programlisting>
+#include <postgres.h>
+#include <fmgr.h>
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plsample_call_handler);
+
+/*
+ * Handle function, procedure, and trigger calls.
+ */
+Datum
+plsample_call_handler(PG_FUNCTION_ARGS)
+{
+ return 0;
+}
+</programlisting>
+ </para>
+
+ <para>
+ The following sections will continue building upon this example to
+ describe how to add additional functionality to a call handler for a
+ procedural language.
+ </para>
+ </sect1>
+
+ <sect1 id="plhandler-source">
+ <title>Fetching the source of a function</title>
+
+ <para>
+ Additional code is added to <filename>plsample.c</filename> from <xref
+ linkend="plhandler-minimal"/> to include additional headers for the
+ additional code that fetches the source text of the function. The
+ resulting file now looks like:
+<programlisting>
+#include <postgres.h>
+#include <fmgr.h>
+#include <funcapi.h>
+#include <access/htup_details.h>
+#include <catalog/pg_proc.h>
+#include <catalog/pg_type.h>
+#include <utils/memutils.h>
+#include <utils/builtins.h>
+#include <utils/syscache.h>
+
+MemoryContext TopMemoryContext = NULL;
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plsample_call_handler);
+
+/*
+ * Handle function, procedure, and trigger calls.
+ */
+
+Datum
+plsample_call_handler(PG_FUNCTION_ARGS)
+{
+ HeapTuple pl_tuple;
+ Datum pl_datum;
+ const char *source;
+ bool isnull;
+
+ /* Fetch the source of the function. */
+
+ pl_tuple = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 0, 0, 0);
+ if (!HeapTupleIsValid(pl_tuple))
+ elog(ERROR, "cache lookup failed for function %u",
+ fcinfo->flinfo->fn_oid);
+
+ pl_datum = SysCacheGetAttr(PROCOID, pl_tuple, Anum_pg_proc_prosrc,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ ReleaseSysCache(pl_tuple);
+
+ source = DatumGetCString(DirectFunctionCall1(textout, pl_datum));
+ elog(LOG, "source text:\n%s", source);
+
+ return 0;
+}
+</programlisting>
+ The variable <structfield>source</structfield> containes the source that
+ needs to be interpreted and executed by the procedurual language handler
+ itself, or by an existing inplementation of the programming language that
+ the source text is written with.
+ </para>
+
+ <para>
+ The following <command>CREATE FUNCTION</command> will set the source text
+ of the function to <literal>This is function's source text.</literal>:
+<programlisting>
+CREATE FUNCTION plsample_func()
+RETURNS VOID
+AS $$
+ This is function's source text.
+$$ LANGUAGE plsample;
+</programlisting>
+ </para>
+
+ <para>
+ Calling the function with the following command will log the function's
+ source text because of the <function>elog()</function> statement towards
+ the end of <function>plsample_call_handler()</function> function:
+<programlisting>
+SELECT plsample_func();
+</programlisting>
+ and emits the following to the log file:
+<screen>
+LOG: source text:
+
+ This is function's source text.
+
+</screen>
+ </para>
+ </sect1>
+
+ <sect1 id="plhandler-arguments">
+ <title>Analyzing function arguments</title>
+
+ <para>
+ This example introduces new code to <filename>plsample.c</filename> from
+ <xref linkend="plhandler-source"/> to demonstrate one method for analyzing
+ the arguments passed to the function. This code iterates through all of
+ the arguments to log its position, name and value as text, regardless of
+ its actual data type for ease of demonstration.
+ </para>
+
+ <para>
+ Call <function>get_func_arg_info()</function> to get a pointer to the
+ argument types, names, and modes before iterating through them to retrieve
+ the value of each argument:
+<programlisting>
+ get_func_arg_info(pl_tuple, &argtypes, &argnames, &argmodes);
+</programlisting>
+ </para>
+
+ <para>
+ The pointer to <structname>FunctionCallInfoBaseData</structname>
+ <type>struct</type> contains the number of argument passed to the function
+ <structfield>flinfo->nargs</structfield>. The following code
+ demostrates how to iterate through each argument and retrieve its value as
+ text:
+<programlisting>
+ elog(LOG, "number of arguments : %d", fcinfo->nargs);
+ for (i = 0; i < fcinfo->nargs; i++)
+ {
+ Oid argtype = pl_struct->proargtypes.values[i];
+ type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
+ if (!HeapTupleIsValid(type_tuple))
+ elog(ERROR, "cache lookup failed for type %u", argtype);
+
+ type_struct = (Form_pg_type) GETSTRUCT(type_tuple);
+ fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt);
+ ReleaseSysCache(type_tuple);
+
+ value = OutputFunctionCall(&arg_out_func[i], fcinfo-<args[i].value);
+
+ elog(LOG, "argument position: %d; name: %s; value: %s", i, argnames[i],
+ value);
+ }
+</programlisting>
+ </para>
+
+ <para>
+ Let's now create a function with three arugments consiting of a number,
+ text and an array of integers:
+<programlisting>
+CREATE FUNCTION plsample_func(a1 NUMERIC, a2 TEXT, a3 INTEGER[])
+RETURNS VOID
+AS $$
+ This is function's source text.
+$$ LANGUAGE plsample;
+</programlisting>
+ Then let's call the function with the following commands:
+<programlisting>
+SELECT plsample_func(1.23, 'abc', '{4, 5, 6}');
+</programlisting>
+ The <function>elog()</function> statements in the example will emit the
+ number of arguments, and the position, name and value of each argument
+ to the log file:
+<screen>
+LOG: number of arguments : 3
+LOG: argument position: 0; name: a1; value: 1.23
+LOG: argument position: 1; name: a2; value: abc
+LOG: argument position: 2; name: a3; value: {4,5,6}
+</screen>
+ </para>
+
+ <para>
+ The complete example of <filename>plsample.c</filename> follows:
+<programlisting>
+#include <postgres.h>
+#include <fmgr.h>
+#include <funcapi.h>
+#include <access/htup_details.h>
+#include <catalog/pg_proc.h>
+#include <catalog/pg_type.h>
+#include <utils/memutils.h>
+#include <utils/builtins.h>
+#include <utils/syscache.h>
+
+MemoryContext TopMemoryContext = NULL;
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plsample_call_handler);
+
+/*
+ * Handle function, procedure, and trigger calls.
+ */
+Datum
+plsample_call_handler(PG_FUNCTION_ARGS)
+{
+ HeapTuple pl_tuple;
+ Datum pl_datum;
+ const char *source;
+ bool isnull;
+
+ int i;
+ FmgrInfo *arg_out_func;
+ Form_pg_type type_struct;
+ HeapTuple type_tuple;
+ Form_pg_proc pl_struct;
+ volatile MemoryContext proc_cxt = NULL;
+ Oid *argtypes;
+ char **argnames;
+ char *argmodes;
+ char *value;
+
+ /* Fetch the source of the function. */
+
+ pl_tuple = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 0, 0, 0);
+ if (!HeapTupleIsValid(pl_tuple))
+ elog(ERROR, "cache lookup failed for function %u",
+ fcinfo->flinfo->fn_oid);
+ pl_struct = (Form_pg_proc) GETSTRUCT(pl_tuple);
+
+ pl_datum = SysCacheGetAttr(PROCOID, pl_tuple, Anum_pg_proc_prosrc,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ ReleaseSysCache(pl_tuple);
+
+ source = DatumGetCString(DirectFunctionCall1(textout, pl_datum));
+ elog(LOG, "source text:\n%s", source);
+
+ arg_out_func = (FmgrInfo *) palloc0(fcinfo->nargs * sizeof(FmgrInfo));
+ proc_cxt = AllocSetContextCreate(TopMemoryContext,
+ "PL/Sample function", 0, (1 * 1024), (8 * 1024));
+ get_func_arg_info(pl_tuple, &argtypes, &argnames, &argmodes);
+
+ /* Iterate through all of the function arguments. */
+ elog(LOG, "number of arguments : %d", fcinfo->nargs);
+ for (i = 0; i < fcinfo->nargs; i++)
+ {
+ Oid argtype = pl_struct->proargtypes.values[i];
+ type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
+ if (!HeapTupleIsValid(type_tuple))
+ elog(ERROR, "cache lookup failed for type %u", argtype);
+
+ type_struct = (Form_pg_type) GETSTRUCT(type_tuple);
+ fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt);
+ ReleaseSysCache(type_tuple);
+
+ value = OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value);
+
+ elog(LOG, "argument position: %d; name: %s; value: %s", i, argnames[i],
+ value);
+ }
+
+ return 0;
+}
+</programlisting>
+ </para>
+ </sect1>
+
+ <sect1 id="plhandler-return">
+ <title>Returning data</title>
+
+ <para>
+ This example introduces new code to <filename>plsample.c</filename> from
+ <xref linkend="plhandler-arguments"/> to demonstrate how to return data
+ from the function. This code fetches the function's return type and
+ attempts to return the function's source text.
+ </para>
+
+ <para>
+ The follow code segments fetches the return type of the function:
+<programlisting>
+ type_tuple = SearchSysCache1(TYPEOID,
+ ObjectIdGetDatum(pl_struct->prorettype));
+ if (!HeapTupleIsValid(type_tuple))
+ elog(ERROR, "cache lookup failed for type %u", pl_struct->prorettype);
+</programlisting>
+ </para>
+
+ <para>
+ The follow code segments takes the function source text, fetched in a
+ previous section, and attempts to return that data:
+<programlisting>
+ ret = InputFunctionCall(&result_in_func, source, result_typioparam, -1);
+ PG_RETURN_DATUM(ret);
+</programlisting>
+ </para>
+
+ <para>
+ The following function can be used to help demostrate the new
+ functionality:
+<programlisting>
+CREATE FUNCTION plsample_func(a1 NUMERIC, a2 TEXT, a3 INTEGER[])
+RETURNS TEXT
+AS $$
+ This is function's source text.
+$$ LANGUAGE plsample;
+</programlisting>
+ It is built upon the function used in the previous examples to now return
+ <type>TEXT</type>. Running the following command returns the source text
+ of the function:
+<programlisting>
+SELECT plsample_func(1.23, 'abc', '{4, 5, 6}');
+</programlisting>
+<screen>
+ plsample_func
+-----------------------------------
+ +
+ This is function's source text.+
+
+(1 row)
+</screen>
+ </para>
+
+ <para>
+ The complete example of <filename>plsample.c</filename> follows:
+<programlisting>
+#include <postgres.h>
+#include <fmgr.h>
+#include <funcapi.h>
+#include <access/htup_details.h>
+#include <catalog/pg_proc.h>
+#include <catalog/pg_type.h>
+#include <utils/memutils.h>
+#include <utils/builtins.h>
+#include <utils/lsyscache.h>
+#include <utils/syscache.h>
+
+MemoryContext TopMemoryContext = NULL;
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plsample_call_handler);
+
+/*
+ * Handle function, procedure, and trigger calls.
+ */
+Datum
+plsample_call_handler(PG_FUNCTION_ARGS)
+{
+ HeapTuple pl_tuple;
+ Datum ret;
+ char *source;
+ bool isnull;
+
+ int i;
+ FmgrInfo *arg_out_func;
+ Form_pg_type type_struct;
+ HeapTuple type_tuple;
+ Form_pg_proc pl_struct;
+ volatile MemoryContext proc_cxt = NULL;
+ Oid *argtypes;
+ char **argnames;
+ char *argmodes;
+ char *value;
+
+ Form_pg_type pg_type_entry;
+ Oid result_typioparam;
+ FmgrInfo result_in_func;
+
+ /* Fetch the source of the function. */
+
+ pl_tuple = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(fcinfo->flinfo->fn_oid), 0, 0, 0);
+ if (!HeapTupleIsValid(pl_tuple))
+ elog(ERROR, "cache lookup failed for function %u",
+ fcinfo->flinfo->fn_oid);
+ pl_struct = (Form_pg_proc) GETSTRUCT(pl_tuple);
+
+ ret = SysCacheGetAttr(PROCOID, pl_tuple, Anum_pg_proc_prosrc, &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ ReleaseSysCache(pl_tuple);
+
+ source = DatumGetCString(DirectFunctionCall1(textout, ret));
+ elog(LOG, "source text:\n%s", source);
+
+ arg_out_func = (FmgrInfo *) palloc0(fcinfo->nargs * sizeof(FmgrInfo));
+ proc_cxt = AllocSetContextCreate(TopMemoryContext,
+ "PL/Sample function", 0, (1 * 1024), (8 * 1024));
+ get_func_arg_info(pl_tuple, &argtypes, &argnames, &argmodes);
+
+ /* Iterate through all of the function arguments. */
+ elog(LOG, "number of arguments : %d", fcinfo->nargs);
+ for (i = 0; i < fcinfo->nargs; i++)
+ {
+ Oid argtype = pl_struct->proargtypes.values[i];
+ type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
+ if (!HeapTupleIsValid(type_tuple))
+ elog(ERROR, "cache lookup failed for type %u", argtype);
+
+ type_struct = (Form_pg_type) GETSTRUCT(type_tuple);
+ fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt);
+ ReleaseSysCache(type_tuple);
+
+ value = OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value);
+
+ elog(LOG, "argument position: %d; name: %s; value: %s", i, argnames[i],
+ value);
+ }
+
+ /* Fetch the return type of the function. */
+
+ type_tuple = SearchSysCache1(TYPEOID,
+ ObjectIdGetDatum(pl_struct->prorettype));
+ if (!HeapTupleIsValid(type_tuple))
+ elog(ERROR, "cache lookup failed for type %u", pl_struct->prorettype);
+
+ pg_type_entry = (Form_pg_type) GETSTRUCT(type_tuple);
+
+ proc_cxt = AllocSetContextCreate(TopMemoryContext, "PL/Sample function",
+ ALLOCSET_SMALL_SIZES);
+
+ result_typioparam = getTypeIOParam(type_tuple);
+
+ fmgr_info_cxt(pg_type_entry->typinput, &result_in_func, proc_cxt);
+ ReleaseSysCache(type_tuple);
+
+ /* Simply return the function source text. */
+ ret = InputFunctionCall(&result_in_func, source, result_typioparam, -1);
+ PG_RETURN_DATUM(ret);
+
+ return 0;
+}
+</programlisting>
+ </para>
+ </sect1>
+
+ <sect1 id="plhandler-notes">
+ <title>Additional notes</title>
+
+ <para>
+ The examples provided in this sections for developing a procedural
+ language handler do not cover all aspects that may need to be considered
+ when introducing a new language. Here are some additional considerations:
+ </para>
+
+ <itemizedlist spacing="compact" mark="bullet">
+ <listitem>
+ <para>
+ Caching the source text of the function for performance reasons.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Handling trigger functions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Validating the source text.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Handling returning sets of rows.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect1>
+
</chapter>
