The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> learn more

5.9.10 Returning Sets

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 ‘funcapi.h’, as above.

A set-returning function (SRF) is called once for each item it returns. The SRF must therefore save enough state to remember what it was doing and return the next item on each call. The structure FuncCallContext is provided to help control this process. Within a function, fcinfo->flinfo->fn_extra is used to hold a pointer to FuncCallContext across calls.

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 This is obsolete and
     only present for backwards compatibility, viz,
     user-defined SRFs that use the deprecated
     TupleDescGetSlot(). */
  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 used
     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;

  /* OPTIONAL pointer to struct containing tuple
     description tuple_desc is for use when returning
     tuples (i.e., composite data types) and is only needed
     if you are going to build the tuples with
     heap_form_tuple() rather than with
     BuildTupleFromCStrings().  Note that the TupleDesc
     pointer stored here should usually have been run
     through BlessTupleDesc() first. */
  TupleDesc tuple_desc;

} FuncCallContext;

An SRF uses several functions and macros that automatically manipulate the FuncCallContext structure (and expect to find it via fn_extra). Use:

SRF_IS_FIRSTCALL()

to determine if your function is being called for the first or a subsequent time. On the first call (only) use:

SRF_FIRSTCALL_INIT()

to initialize the FuncCallContext. On every function call, including the first, use:

SRF_PERCALL_SETUP()

to properly set up for using the FuncCallContext and clearing any previously returned data left over from the previous pass.

If your function has data to return, use:

SRF_RETURN_NEXT(funcctx, result)

to return it to the caller. (result must be of type Datum, either a single value or a tuple prepared as described above.) Finally, when your function is finished returning data, use:

SRF_RETURN_DONE(funcctx)

to clean up and end the SRF.

The memory context that is current when the SRF is called is a transient context that will be cleared between calls. This means that you do not need to call pfree on everything you allocated using 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 multi_call_memory_ctx is a suitable location for any data that needs to survive until the SRF is finished running. In most cases, this means that you should switch into multi_call_memory_ctx while doing the first-call setup.

A complete pseudo-code example looks like the following:

Datum
my_set_returning_function(PG_FUNCTION_ARGS)
{
  FuncCallContext *funcctx;
  Datum result;
  further declarations as needed if (SRF_IS_FIRSTCALL()) {
    MemoryContext oldcontext;

    funcctx = SRF_FIRSTCALL_INIT();
    oldcontext =
        MemoryContextSwitchTo(funcctx->
                              multi_call_memory_ctx);
    /* One-time setup code appears here: */
    <<user code>>
    <<if returning composite>>
    <<build TupleDesc, and perhaps AttInMetadata>>
    <<endif returning composite>>
    <<user code>>
    MemoryContextSwitchTo(oldcontext);
  }

  /* Each-time setup code appears here: */
  <<user code>>
  funcctx = SRF_PERCALL_SETUP();
  <<user code>>

  /* this is just one way we might test whether we are
     done: */
  if (funcctx->call_cntr < funcctx->max_calls) {
    /* Here we want to return another item: */
    <<user code>>
    <<obtain result Datum>>
    SRF_RETURN_NEXT(funcctx, result);
  } else {
    /* Here we are done returning items and just need to
       clean up: */
    <<user code>>
    SRF_RETURN_DONE(funcctx);
  }
}

A complete example of a simple SRF returning a composite type looks like:

PG_FUNCTION_INFO_V1(retcomposite);

Datum
retcomposite(PG_FUNCTION_ARGS)
{
  FuncCallContext *funcctx;
  int call_cntr;
  int max_calls;
  TupleDesc tupdesc;
  AttInMetadata *attinmeta;

  /* stuff done only on the first call of the function */
  if (SRF_IS_FIRSTCALL()) {
    MemoryContext oldcontext;

    /* create a function context for cross-call persistence
     */
    funcctx = SRF_FIRSTCALL_INIT();

    /* switch to memory context appropriate for multiple
       function calls */
    oldcontext =
        MemoryContextSwitchTo(funcctx->
                              multi_call_memory_ctx);

    /* total number of tuples to be returned */
    funcctx->max_calls = PG_GETARG_UINT32(0);

    /* Build a tuple descriptor for our result type */
    if (get_call_result_type(fcinfo, NULL, &tupdesc) !=
        TYPEFUNC_COMPOSITE)
      ereport(ERROR,
              (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
               errmsg
               ("function returning record called in context "
                "that cannot accept type record")));

    /* generate attribute metadata needed later to produce
       tuples from raw C strings */
    attinmeta = TupleDescGetAttInMetadata(tupdesc);
    funcctx->attinmeta = attinmeta;

    MemoryContextSwitchTo(oldcontext);
  }

  /* stuff done on every call of the function */
  funcctx = SRF_PERCALL_SETUP();

  call_cntr = funcctx->call_cntr;
  max_calls = funcctx->max_calls;
  attinmeta = funcctx->attinmeta;

  if (call_cntr < max_calls) {  /* do when there is more
                                   left to send */
    char **values;
    HeapTuple tuple;
    Datum result;

    /* Prepare a values array for building the returned
       tuple. This should be an array of C strings which
       will be processed later by the type input functions. */
    values = (char **) palloc(3 * sizeof(char *));
    values[0] = (char *) palloc(16 * sizeof(char));
    values[1] = (char *) palloc(16 * sizeof(char));
    values[2] = (char *) palloc(16 * sizeof(char));

    snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
    snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
    snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));

    /* build a tuple */
    tuple = BuildTupleFromCStrings(attinmeta, values);

    /* make the tuple into a datum */
    result = HeapTupleGetDatum(tuple);

    /* clean up (this is not really necessary) */
    pfree(values[0]);
    pfree(values[1]);
    pfree(values[2]);
    pfree(values);

    SRF_RETURN_NEXT(funcctx, result);
  } else {  /* do when there is no more left */

    SRF_RETURN_DONE(funcctx);
  }
}

One way to declare this function in SQL is:

CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 
  integer);

CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
    RETURNS SETOF __retcomposite
    AS 'filename', 'retcomposite'
    LANGUAGE C IMMUTABLE STRICT;

A different way is to use OUT parameters:

CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
    OUT f1 integer, OUT f2 integer, OUT f3 integer)
    RETURNS SETOF record
    AS 'filename', 'retcomposite'
    LANGUAGE C IMMUTABLE STRICT;

Notice that in this method the output type of the function is formally an anonymous record type.

The directory ‘contrib/tablefunc’ in the source distribution contains more examples of set-returning functions.