xfunc.sgml

PostgreSQL7.4.6 for Linux

        with each other or with symbols defined in the
        <productname>PostgreSQL</productname> server executable.  You
        will have to rename your functions or variables if you get
        error messages to this effect.
       </para>
      </listitem>

      <listitem>
       <para>
        Compiling and linking your code so that it can be dynamically
        loaded into <productname>PostgreSQL</productname> always
        requires special flags.  See <xref linkend="dfunc"> for a
        detailed explanation of how to do it for your particular
        operating system.
       </para>
      </listitem>
     </itemizedlist>
    </para>
   </sect2>

&dfunc;

   <sect2>
    <title>Composite-Type Arguments in C-Language Functions</title>

    <para>
     Composite types do not  have  a  fixed  layout  like  C
     structures.   Instances of a composite type may contain
     null fields.  In addition,  composite  types  that  are
     part  of  an  inheritance  hierarchy may have different
     fields than other members of the same inheritance hierarchy.    
     Therefore,  <productname>PostgreSQL</productname>  provides  
     a function interface for accessing fields of composite types  
     from C.
    </para>

    <para>
     Suppose we want to write a function to answer the query

<programlisting>
SELECT name, c_overpaid(emp, 1500) AS overpaid
    FROM emp
    WHERE name = 'Bill' OR name = 'Sam';
</programlisting>

     Using call conventions version 0, we can define
     <function>c_overpaid</> as:
     
<programlisting>
#include "postgres.h"
#include "executor/executor.h"  /* for GetAttributeByName() */

bool
c_overpaid(TupleTableSlot *t, /* the current row of emp */
           int32 limit)
{
    bool isnull;
    int32 salary;

    salary = DatumGetInt32(GetAttributeByName(t, "salary", &amp;isnull));
    if (isnull)
        return false;
    return salary &gt; limit;
}
</programlisting>

     In version-1 coding, the above would look like this:

<programlisting>
#include "postgres.h"
#include "executor/executor.h"  /* for GetAttributeByName() */

PG_FUNCTION_INFO_V1(c_overpaid);

Datum
c_overpaid(PG_FUNCTION_ARGS)
{
    TupleTableSlot  *t = (TupleTableSlot *) PG_GETARG_POINTER(0);
    int32            limit = PG_GETARG_INT32(1);
    bool isnull;
    int32 salary;

    salary = DatumGetInt32(GetAttributeByName(t, "salary", &amp;isnull));
    if (isnull)
        PG_RETURN_BOOL(false);
    /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */

    PG_RETURN_BOOL(salary &gt; limit);
}
</programlisting>
    </para>

    <para>
     <function>GetAttributeByName</function> is the 
     <productname>PostgreSQL</productname> system function that
     returns attributes out of the specified row.  It has
     three arguments: the argument of type <type>TupleTableSlot*</type> passed into
     the  function, the name of the desired attribute, and a
     return parameter that tells whether  the  attribute
     is  null.   <function>GetAttributeByName</function> returns a <type>Datum</type>
     value that you can convert to the proper data type by using the
     appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function> macro.
    </para>

    <para>
     The following command declares the function
     <function>c_overpaid</function> in SQL:

<programlisting>
CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
    AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
    LANGUAGE C;
</programlisting>
    </para>
   </sect2>

   <sect2>
    <title>Returning Rows (Composite Types) from C-Language Functions</title>

    <para>
     To return a row or composite-type value from a C-language
     function, you can use a special API that provides macros and
     functions to hide most of the complexity of building composite
     data types.  To use this API, the source file must include:
<programlisting>
#include "funcapi.h"
</programlisting>
    </para>

    <para>
     The support for returning composite data types (or rows) starts
     with the <structname>AttInMetadata</> structure. This structure
     holds arrays of individual attribute information needed to create
     a row from raw C strings. The information contained in the
     structure is derived from a <structname>TupleDesc</> structure,
     but it is stored to avoid redundant computations on each call to
     a set-returning function (see next section).  In the case of a
     function returning a set, the <structname>AttInMetadata</>
     structure should be computed once during the first call and saved
     for reuse in later calls.  <structname>AttInMetadata</> also
     saves a pointer to the original <structname>TupleDesc</>.
<programlisting>
typedef struct AttInMetadata
{
    /* full TupleDesc */
    TupleDesc       tupdesc;

    /* array of attribute type input function finfo */
    FmgrInfo       *attinfuncs;

    /* array of attribute type typelem */
    Oid            *attelems;

    /* array of attribute typmod */
    int32    	   *atttypmods;
}	AttInMetadata;
</programlisting>
    </para>

    <para>
     To assist you in populating this structure, several functions and a macro
     are available. Use
<programlisting>
TupleDesc RelationNameGetTupleDesc(const char *relname)
</programlisting>
     to get a <structname>TupleDesc</> for a named relation, or
<programlisting>
TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
</programlisting>
     to get a <structname>TupleDesc</> based on a type OID. This can
     be used to get a <structname>TupleDesc</> for a base or
     composite type. Then
<programlisting>
AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
</programlisting>
     will return a pointer to an <structname>AttInMetadata</>,
     initialized based on the given
     <structname>TupleDesc</>. <structname>AttInMetadata</> can be
     used in conjunction with C strings to produce a properly formed
     row value (internally called tuple).
    </para>

    <para>
     To return a tuple you must create a tuple slot based on the
     <structname>TupleDesc</>. You can use
<programlisting>
TupleTableSlot *TupleDescGetSlot(TupleDesc tupdesc)
</programlisting>
     to initialize this tuple slot, or obtain one through other (user provided)
     means. The tuple slot is needed to create a <type>Datum</> for return by the
     function.  The same slot can (and should) be reused on each call.
    </para>

    <para>
     After constructing an <structname>AttInMetadata</> structure,
<programlisting>
HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
</programlisting>
     can be used to build a <structname>HeapTuple</> given user data
     in C string form.  <literal>values</literal> is an array of C strings, one for
     each attribute of the return row. Each C string should be in
     the form expected by the input function of the attribute data
     type. In order to return a null value for one of the attributes,
     the corresponding pointer in the <parameter>values</> array
     should be set to <symbol>NULL</>.  This function will need to
     be called again for each row you return.
    </para>

    <para>
     Building a tuple via <function>TupleDescGetAttInMetadata</> and
     <function>BuildTupleFromCStrings</> is only convenient if your
     function naturally computes the values to be returned as text
     strings.  If your code naturally computes the values as a set of
     <type>Datum</> values, you should instead use the underlying
     function <function>heap_formtuple</> to convert the
     <type>Datum</type> values directly into a tuple.  You will still need
     the <structname>TupleDesc</> and a <structname>TupleTableSlot</>,
     but not <structname>AttInMetadata</>.
    </para>

    <para>
     Once you have built a tuple to return from your function, it
     must be converted into a <type>Datum</>. Use
<programlisting>
TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple)
</programlisting>
     to get a <type>Datum</> given a tuple and a slot.  This
     <type>Datum</> can be returned directly if you intend to return
     just a single row, or it can be used as the current return value
     in a set-returning function.
    </para>

    <para>
     An example appears in the next section.
    </para>

   </sect2>

   <sect2 id="xfunc-c-return-set">
    <title>Returning Sets from C-Language Functions</title>

    <para>
     There is also a special API that provides support for returning
     sets (multiple rows) from a C-language function.  A set-returning
     function must follow the version-1 calling conventions.  Also,
     source files must include <filename>funcapi.h</filename>, as
     above.
    </para>

    <para>
     A set-returning function (<acronym>SRF</>) is called
     once for each item it returns.  The <acronym>SRF</> must
     therefore save enough state to remember what it was doing and
     return the next item on each call.
     The structure <structname>FuncCallContext</> is provided to help
     control this process.  Within a function, <literal>fcinfo-&gt;flinfo-&gt;fn_extra</>
     is used to hold a pointer to <structname>FuncCallContext</>
     across calls.
<programlisting>
typedef struct
{
    /*
     * Number of times we've been called before
     * 
     * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
     * incremented for you every time SRF_RETURN_NEXT() is called.
     */
    uint32 call_cntr;

    /*
     * OPTIONAL maximum number of calls
     *
     * max_calls is here for convenience only and setting it is optional.
     * If not set, you must provide alternative means to know when the
     * function is done.
     */
    uint32 max_calls;

    /*
     * OPTIONAL pointer to result slot
     * 
     * slot is for use when returning tuples (i.e., composite data types)
     * and is not needed when returning base data types.
     */
    TupleTableSlot *slot;

    /*
     * OPTIONAL pointer to miscellaneous user-provided context information
     * 
     * user_fctx is for use as a pointer to your own data to retain
     * arbitrary context information between calls of your function.
     */
    void *user_fctx;

    /*
     * OPTIONAL pointer to struct containing attribute type input metadata
     * 
     * attinmeta is for use when returning tuples (i.e., composite data types)
     * and is not needed when returning base data types. It
     * is only needed if you intend to use BuildTupleFromCStrings() to create
     * the return tuple.
     */
    AttInMetadata *attinmeta;

    /*
     * memory context used for structures that must live for multiple calls
     *
     * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
     * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
     * context for any memory that is to be reused across multiple calls
     * of the SRF.
     */
    MemoryContext multi_call_memory_ctx;
} FuncCallContext;
</programlisting>
    </para>

    <para>
     An <acronym>SRF</> uses several functions and macros that
     automatically manipulate the <structname>FuncCallContext</>
     structure (and expect to find it via <literal>fn_extra</>).  Use
<programlisting>
SRF_IS_FIRSTCALL()
</programlisting>
     to determine if your function is being called for the first or a
     subsequent time. On the first call (only) use
<programlisting>
SRF_FIRSTCALL_INIT()
</programlisting>
     to initialize the <structname>FuncCallContext</>. On every function call,
     including the first, use
<programlisting>
SRF_PERCALL_SETUP()
</programlisting>
     to properly set up for using the <structname>FuncCallContext</>
     and clearing any previously returned data left over from the
     previous pass.
    </para>

    <para>
     If your function has data to return, use
<programlisting>
SRF_RETURN_NEXT(funcctx, result)
</programlisting>
     to return it to the caller.  (<literal>result</> must be of type
     <type>Datum</>, either a single value or a tuple prepared as
     described above.)  Finally, when your function is finished
     returning data, use
<programlisting>
SRF_RETURN_DONE(funcctx)
</programlisting>
     to clean up and end the <acronym>SRF</>.
    </para>

    <para>
     The memory context that is current when the <acronym>SRF</> is called is
     a transient context that will be cleared between calls.  This means
     that you do not need to call <function>pfree</> on everything
     you allocated using <function>palloc</>; it will go away anyway.  However, if you want to allocate
     any data structures to live across calls, you need to put them somewhere
     else.  The memory context referenced by
     <structfield>multi_call_memory_ctx</> is a suitable location for any
     data that needs to survive until the <acronym>SRF</> is finished running.  In most
     cases, this means that you should switch into
     <structfield>multi_call_memory_ctx</> while doing the first-call setup.
    </para>

    <para>
     A complete pseudo-code example looks like the following:
<programlisting>
Datum
my_set_returning_function(PG_FUNCTION_ARGS)
{
    FuncCallContext  *funcctx;
    Datum             result;
    MemoryContext     oldcontext;
    <replaceable>further declarations as needed</replaceable>

    if (SRF_IS_FIRSTCALL())
    {
        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        /* One-time setup code appears here: */
        <replaceable>user code</replaceable>
        <replaceable>if returning composite</replaceable>
            <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
            <replaceable>obtain slot</replaceable>
            funcctx-&gt;slot = slot;
        <r