Table Function API doc patch

Here (finally ;-)) is a doc patch covering the Table Function C API. It
reflects the changes in the tablefunc-fix patch that I sent in the other
day. It also refers to "see contrib/tablefunc for more examples", which
is next on my list of things to finish and submit.

If there are no objections, please apply.

Thanks,

Joe

Are table functions going to make it into pl/pgsql fro 7.3? I recall there
was a discussion of the syntax, but was any decision made upon a final
syntax?

Chris

> -----Original Message-----
> From: pgsql-patches-owner(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Joe Conway
> Sent: Thursday, 11 July 2002 7:28 AM
> To: pgsql-patches(at)postgresql(dot)org
> Subject: [PATCHES] Table Function API doc patch
>
>
> Here (finally ;-)) is a doc patch covering the Table Function C API. It
> reflects the changes in the tablefunc-fix patch that I sent in the other
> day. It also refers to "see contrib/tablefunc for more examples", which
> is next on my list of things to finish and submit.
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe
>

Christopher Kings-Lynne wrote:
> Are table functions going to make it into pl/pgsql fro 7.3? I recall there
> was a discussion of the syntax, but was any decision made upon a final
> syntax?
>

Well, it's still on my list. Sorry I never responded to your question
the other day about peoples' queues, but mine looks like:

1. contrib/tablefunc -- some examples of usage of the Table Function
API. Per previous deiscussions, this will include some form of
(probably renamed) show_all_vars(), as well one or two others. This
is pretty far along already.
2. other GUC related change discussed here:
http://archives.postgresql.org/pgsql-patches/2002-06/msg00229.php
Still lots to do yet here.
3. PL/pgSQL support for Table Functions -- syntax and other details are
yet to be discussed in any real detail.
4. Changes to contrib/dblink to make use of the Table Function API, and
support for cursors.

I'm not sure that this will all get done in time for 7.3 or not, but I
will for sure try.

Joe

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Here (finally ;-)) is a doc patch covering the Table Function C API. It
> reflects the changes in the tablefunc-fix patch that I sent in the other
> day. It also refers to "see contrib/tablefunc for more examples", which
> is next on my list of things to finish and submit.
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe

[ text/html is unsupported, treating like TEXT/PLAIN ]

> Index: doc/src/sgml/xfunc.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> retrieving revision 1.52
> diff -c -r1.52 xfunc.sgml
> *** doc/src/sgml/xfunc.sgml 20 Jun 2002 16:57:00 -0000 1.52
> --- doc/src/sgml/xfunc.sgml 10 Jul 2002 22:53:23 -0000
> ***************
> *** 1461,1472 ****
> LANGUAGE C;
> </programlisting>
> </para>
>
> <para>
> ! While there are ways to construct new rows or modify
> ! existing rows from within a C function, these
> ! are far too complex to discuss in this manual.
> ! Consult the backend source code for examples.
> </para>
> </sect2>
>
> --- 1461,1808 ----
> LANGUAGE C;
> </programlisting>
> </para>
> + </sect2>
> +
> + <sect2>
> + <title>Table Function API</title>
> +
> + <para>
> + The Table Function API assists in the creation of a user defined
> + C Language table functions (<xref linkend="xfunc-tablefunctions">).
> + Table functions are functions that produce a set of rows, made up of
> + either base (scalar) data types, or composite (multi-column) data types.
> + The API is split into two main components: support for returning
> + composite data types, and support for returning multiple rows
> + (set returning functions or SRFs).
> + </para>
> +
> + <para>
> + The Table Function API relies on macros and functions to suppress most
> + of the complexity of building composite data types and return multiple
> + results. In addition to the version-1 conventions discussed elsewhere,
> + a table function always requires the following:
> + <programlisting>
> + #include "funcapi.h"
> + </programlisting>
> + </para>
> +
> + <para>
> + The Table Function API support for returning composite data types
> + (or tuples) starts with the AttInMetadata struct. This struct holds
> + arrays of individual attribute information needed to create a tuple from
> + raw C strings. It also requires a copy of the TupleDesc. The information
> + carried here is derived from the TupleDesc, but it is stored here to
> + avoid redundant cpu cycles on each call to a Table Function.
> + <programlisting>
> + typedef struct
> + {
> + /* full TupleDesc */
> + TupleDesc tupdesc;
> +
> + /* pointer to array of attribute "type"in finfo */
> + FmgrInfo *attinfuncs;
> +
> + /* pointer to array of attribute type typelem */
> + Oid *attelems;
> +
> + /* pointer to array of attribute type typtypmod */
> + int4 *atttypmods;
> +
> + } AttInMetadata;
> + </programlisting>
> + To assist you in populating this struct, several functions and a macro
> + are available. Use
> + <programlisting>
> + TupleDesc RelationNameGetTupleDesc(char *relname)
> + </programlisting>
> + to get a TupleDesc based on the function's return type relation, or
> + <programlisting>
> + TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
> + </programlisting>
> + to get a TupleDesc based on the function's type oid. This can be used to
> + get a TupleDesc for a base (scalar), or composite (relation) type. Then
> + <programlisting>
> + AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
> + </programlisting>
> + will return a pointer to an AttInMetadata struct, initialized based on
> + the function's TupleDesc. AttInMetadata is be used in conjunction with
> + C strings to produce a properly formed tuple. The metadata is stored here
> + for use across calls to avoid redundant work.
> + </para>
> +
> + <para>
> + In order to return a tuple you must create a tuple slot based on the
> + 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 Datum for return by the
> + function.
> + </para>
> +
> + <para>
> + If desired,
> + <programlisting>
> + HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
> + </programlisting>
> + can be used to build a HeapTuple given user data in C string form.
> + "values" is an array of C strings, one for each attribute of the return
> + tuple. The C strings should be in the form expected by the "in" function
> + of the attribute data type. For more information on this requirement,
> + see the individual data type "in" functions in the source code
> + (e.g. textin() for data type TEXT). In order to return a NULL value for
> + one of the attributes, the corresponding pointer in the "values" array
> + should be set to NULL.
> + </para>
> +
> + <para>
> + Finally, in order to return a tuple using the SRF portion of the API
> + (described below), the tuple must be converted into a Datum. Use
> + <programlisting>
> + TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple)
> + </programlisting>
> + to get a Datum given a tuple and a slot.
> + </para>
> +
> + <para>
> + The Table Function API support for set returning functions starts with
> + the FuncCallContext struct. This struct holds function context for
> + SRFs using fcinfo->flinfo->fn_extra to hold a pointer to it 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 (i.e. scalar) data types.
> + */
> + TupleTableSlot *slot;
> +
> + /*
> + * OPTIONAL pointer to misc user provided context info
> + *
> + * user_fctx is for use as a pointer to your own struct to retain
> + * arbitrary context information between calls for your function.
> + */
> + void *user_fctx;
> +
> + /*
> + * OPTIONAL pointer to struct containing arrays of attribute type input
> + * metainfo
> + *
> + * attinmeta is for use when returning tuples (i.e. composite data types)
> + * and is not needed when returning base (i.e. scalar) data types. It
> + * is ONLY needed if you intend to use BuildTupleFromCStrings() to create
> + * the return tuple.
> + */
> + AttInMetadata *attinmeta;
> +
> + /*
> + * memory context used to initialize structure
> + *
> + * fmctx is set by SRF_FIRSTCALL_INIT() for you, and used by
> + * SRF_RETURN_DONE() for cleanup. It is primarily for internal use
> + * by the API.
> + */
> + MemoryContext fmctx;
> +
> + } FuncCallContext;
> + </programlisting>
> + To assist you in populating this struct, several functions and macros
> + are available. Use
> + <programlisting>
> + SRF_IS_FIRSTCALL()
> + </programlisting>
> + to determine if your function has been called for the first or a
> + subsequent time. On the first call (only) use
> + <programlisting>
> + SRF_FIRSTCALL_INIT()
> + </programlisting>
> + to initialize the FuncCallContext struct. On every function call,
> + including the first, use
> + <programlisting>
> + SRF_PERCALL_SETUP()
> + </programlisting>
> + to properly set up for using the FuncCallContext struct 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 send it and prepare for the next call. Finally, when your function
> + is finished returning data, use
> + <programlisting>
> + SRF_RETURN_DONE(funcctx)
> + </programlisting>
> + to clean up and end the SRF.
> + </para>
> +
> + <para>
> + A complete pseudo-code example looks like the following:
> + <programlisting>
> + Datum
> + my_Set_Returning_Function(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + Datum result;
> +
> + [user defined declarations]
> +
> + if(SRF_IS_FIRSTCALL())
> + {
> + [user defined code]
> + funcctx = SRF_FIRSTCALL_INIT();
> + [if returning composite]
> + [obtain slot]
> + funcctx->slot = slot;
> + [endif returning composite]
> + [user defined code]
> + }
> + [user defined code]
> + funcctx = SRF_PERCALL_SETUP();
> + [user defined code]
> +
> + if (funcctx->call_cntr < funcctx->max_calls)
> + {
> + [user defined code]
> + [obtain result Datum]
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + </programlisting>
> + </para>
> +
> + <para>
> + An example of a simple composite returning SRF looks like:
> + <programlisting>
> + PG_FUNCTION_INFO_V1(testpassbyval);
> + Datum
> + testpassbyval(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + TupleDesc tupdesc;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /*
> + * Build a tuple description for a __testpassbyval tuple
> + */
> + tupdesc = RelationNameGetTupleDesc("__testpassbyval");
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + 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 storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" 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 = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + 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);
> + }
> + }
> + </programlisting>
> + with supporting SQL code of
> + <programlisting>
> + CREATE VIEW __testpassbyval AS
> + SELECT
> + 0::INT4 AS f1,
> + 0::INT4 AS f2,
> + 0::INT4 AS f3;
> +
> + CREATE OR REPLACE FUNCTION testpassbyval(int4, int4) RETURNS setof __testpassbyval
> + AS 'MODULE_PATHNAME','testpassbyval' LANGUAGE 'c' IMMUTABLE STRICT;
> + </programlisting>
> + </para>
>
> <para>
> ! See contrib/tablefunc for more examples of Table Functions.
> </para>
> </sect2>
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Here (finally ;-)) is a doc patch covering the Table Function C API. It
> reflects the changes in the tablefunc-fix patch that I sent in the other
> day. It also refers to "see contrib/tablefunc for more examples", which
> is next on my list of things to finish and submit.
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe

[ text/html is unsupported, treating like TEXT/PLAIN ]

> Index: doc/src/sgml/xfunc.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> retrieving revision 1.52
> diff -c -r1.52 xfunc.sgml
> *** doc/src/sgml/xfunc.sgml 20 Jun 2002 16:57:00 -0000 1.52
> --- doc/src/sgml/xfunc.sgml 10 Jul 2002 22:53:23 -0000
> ***************
> *** 1461,1472 ****
> LANGUAGE C;
> </programlisting>
> </para>
>
> <para>
> ! While there are ways to construct new rows or modify
> ! existing rows from within a C function, these
> ! are far too complex to discuss in this manual.
> ! Consult the backend source code for examples.
> </para>
> </sect2>
>
> --- 1461,1808 ----
> LANGUAGE C;
> </programlisting>
> </para>
> + </sect2>
> +
> + <sect2>
> + <title>Table Function API</title>
> +
> + <para>
> + The Table Function API assists in the creation of a user defined
> + C Language table functions (<xref linkend="xfunc-tablefunctions">).
> + Table functions are functions that produce a set of rows, made up of
> + either base (scalar) data types, or composite (multi-column) data types.
> + The API is split into two main components: support for returning
> + composite data types, and support for returning multiple rows
> + (set returning functions or SRFs).
> + </para>
> +
> + <para>
> + The Table Function API relies on macros and functions to suppress most
> + of the complexity of building composite data types and return multiple
> + results. In addition to the version-1 conventions discussed elsewhere,
> + a table function always requires the following:
> + <programlisting>
> + #include "funcapi.h"
> + </programlisting>
> + </para>
> +
> + <para>
> + The Table Function API support for returning composite data types
> + (or tuples) starts with the AttInMetadata struct. This struct holds
> + arrays of individual attribute information needed to create a tuple from
> + raw C strings. It also requires a copy of the TupleDesc. The information
> + carried here is derived from the TupleDesc, but it is stored here to
> + avoid redundant cpu cycles on each call to a Table Function.
> + <programlisting>
> + typedef struct
> + {
> + /* full TupleDesc */
> + TupleDesc tupdesc;
> +
> + /* pointer to array of attribute "type"in finfo */
> + FmgrInfo *attinfuncs;
> +
> + /* pointer to array of attribute type typelem */
> + Oid *attelems;
> +
> + /* pointer to array of attribute type typtypmod */
> + int4 *atttypmods;
> +
> + } AttInMetadata;
> + </programlisting>
> + To assist you in populating this struct, several functions and a macro
> + are available. Use
> + <programlisting>
> + TupleDesc RelationNameGetTupleDesc(char *relname)
> + </programlisting>
> + to get a TupleDesc based on the function's return type relation, or
> + <programlisting>
> + TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
> + </programlisting>
> + to get a TupleDesc based on the function's type oid. This can be used to
> + get a TupleDesc for a base (scalar), or composite (relation) type. Then
> + <programlisting>
> + AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
> + </programlisting>
> + will return a pointer to an AttInMetadata struct, initialized based on
> + the function's TupleDesc. AttInMetadata is be used in conjunction with
> + C strings to produce a properly formed tuple. The metadata is stored here
> + for use across calls to avoid redundant work.
> + </para>
> +
> + <para>
> + In order to return a tuple you must create a tuple slot based on the
> + 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 Datum for return by the
> + function.
> + </para>
> +
> + <para>
> + If desired,
> + <programlisting>
> + HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
> + </programlisting>
> + can be used to build a HeapTuple given user data in C string form.
> + "values" is an array of C strings, one for each attribute of the return
> + tuple. The C strings should be in the form expected by the "in" function
> + of the attribute data type. For more information on this requirement,
> + see the individual data type "in" functions in the source code
> + (e.g. textin() for data type TEXT). In order to return a NULL value for
> + one of the attributes, the corresponding pointer in the "values" array
> + should be set to NULL.
> + </para>
> +
> + <para>
> + Finally, in order to return a tuple using the SRF portion of the API
> + (described below), the tuple must be converted into a Datum. Use
> + <programlisting>
> + TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple)
> + </programlisting>
> + to get a Datum given a tuple and a slot.
> + </para>
> +
> + <para>
> + The Table Function API support for set returning functions starts with
> + the FuncCallContext struct. This struct holds function context for
> + SRFs using fcinfo->flinfo->fn_extra to hold a pointer to it 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 (i.e. scalar) data types.
> + */
> + TupleTableSlot *slot;
> +
> + /*
> + * OPTIONAL pointer to misc user provided context info
> + *
> + * user_fctx is for use as a pointer to your own struct to retain
> + * arbitrary context information between calls for your function.
> + */
> + void *user_fctx;
> +
> + /*
> + * OPTIONAL pointer to struct containing arrays of attribute type input
> + * metainfo
> + *
> + * attinmeta is for use when returning tuples (i.e. composite data types)
> + * and is not needed when returning base (i.e. scalar) data types. It
> + * is ONLY needed if you intend to use BuildTupleFromCStrings() to create
> + * the return tuple.
> + */
> + AttInMetadata *attinmeta;
> +
> + /*
> + * memory context used to initialize structure
> + *
> + * fmctx is set by SRF_FIRSTCALL_INIT() for you, and used by
> + * SRF_RETURN_DONE() for cleanup. It is primarily for internal use
> + * by the API.
> + */
> + MemoryContext fmctx;
> +
> + } FuncCallContext;
> + </programlisting>
> + To assist you in populating this struct, several functions and macros
> + are available. Use
> + <programlisting>
> + SRF_IS_FIRSTCALL()
> + </programlisting>
> + to determine if your function has been called for the first or a
> + subsequent time. On the first call (only) use
> + <programlisting>
> + SRF_FIRSTCALL_INIT()
> + </programlisting>
> + to initialize the FuncCallContext struct. On every function call,
> + including the first, use
> + <programlisting>
> + SRF_PERCALL_SETUP()
> + </programlisting>
> + to properly set up for using the FuncCallContext struct 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 send it and prepare for the next call. Finally, when your function
> + is finished returning data, use
> + <programlisting>
> + SRF_RETURN_DONE(funcctx)
> + </programlisting>
> + to clean up and end the SRF.
> + </para>
> +
> + <para>
> + A complete pseudo-code example looks like the following:
> + <programlisting>
> + Datum
> + my_Set_Returning_Function(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + Datum result;
> +
> + [user defined declarations]
> +
> + if(SRF_IS_FIRSTCALL())
> + {
> + [user defined code]
> + funcctx = SRF_FIRSTCALL_INIT();
> + [if returning composite]
> + [obtain slot]
> + funcctx->slot = slot;
> + [endif returning composite]
> + [user defined code]
> + }
> + [user defined code]
> + funcctx = SRF_PERCALL_SETUP();
> + [user defined code]
> +
> + if (funcctx->call_cntr < funcctx->max_calls)
> + {
> + [user defined code]
> + [obtain result Datum]
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + </programlisting>
> + </para>
> +
> + <para>
> + An example of a simple composite returning SRF looks like:
> + <programlisting>
> + PG_FUNCTION_INFO_V1(testpassbyval);
> + Datum
> + testpassbyval(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + TupleDesc tupdesc;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /*
> + * Build a tuple description for a __testpassbyval tuple
> + */
> + tupdesc = RelationNameGetTupleDesc("__testpassbyval");
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + 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 storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" 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 = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + 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);
> + }
> + }
> + </programlisting>
> + with supporting SQL code of
> + <programlisting>
> + CREATE VIEW __testpassbyval AS
> + SELECT
> + 0::INT4 AS f1,
> + 0::INT4 AS f2,
> + 0::INT4 AS f3;
> +
> + CREATE OR REPLACE FUNCTION testpassbyval(int4, int4) RETURNS setof __testpassbyval
> + AS 'MODULE_PATHNAME','testpassbyval' LANGUAGE 'c' IMMUTABLE STRICT;
> + </programlisting>
> + </para>
>
> <para>
> ! See contrib/tablefunc for more examples of Table Functions.
> </para>
> </sect2>
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Joe Conway wrote:
> Here (finally ;-)) is a doc patch covering the Table Function C API. It
> reflects the changes in the tablefunc-fix patch that I sent in the other
> day. It also refers to "see contrib/tablefunc for more examples", which
> is next on my list of things to finish and submit.

As mentioned above, here is my contrib/tablefunc patch. It includes
three functions which exercise the tablefunc API.

show_all_settings()
- returns the same information as SHOW ALL, but as a query result

normal_rand(int numvals, float8 mean, float8 stddev, int seed)
- returns a set of normally distributed float8 values
- This routine implements Algorithm P (Polar method for normal
deviates) from Knuth's _The_Art_of_Computer_Programming_, Volume 2,
3rd ed., pages 122-126. Knuth cites his source as "The polar
method", G. E. P. Box, M. E. Muller, and G. Marsaglia,
_Annals_Math,_Stat._ 29 (1958), 610-611.

crosstabN(text sql)
- returns a set of row_name plus N category value columns
- crosstab2(), crosstab3(), and crosstab4() are defined for you,
but you can create additional crosstab functions per directions
in the README.

crosstabN example usage

create table ct(id serial, rowclass text, rowid text, attribute text,
value text);
insert into ct(rowclass, rowid, attribute, value)
values('group1','test1','att1','val1');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test1','att2','val2');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test1','att3','val3');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test1','att4','val4');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test2','att1','val5');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test2','att2','val6');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test2','att3','val7');
insert into ct(rowclass, rowid, attribute, value)
values('group1','test2','att4','val8');

select * from crosstab3(
'select rowid, attribute, value
from ct
where rowclass = ''group1''
and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');

row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
(2 rows)

Note that this patch depends on the guc_and_tablefunc patch I sent in a
few minutes ago.

Please apply if no objections.

Thanks,

Joe

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Joe Conway wrote:
> > Here (finally ;-)) is a doc patch covering the Table Function C API. It
> > reflects the changes in the tablefunc-fix patch that I sent in the other
> > day. It also refers to "see contrib/tablefunc for more examples", which
> > is next on my list of things to finish and submit.
>
> As mentioned above, here is my contrib/tablefunc patch. It includes
> three functions which exercise the tablefunc API.
>
> show_all_settings()
> - returns the same information as SHOW ALL, but as a query result
>
> normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> - returns a set of normally distributed float8 values
> - This routine implements Algorithm P (Polar method for normal
> deviates) from Knuth's _The_Art_of_Computer_Programming_, Volume 2,
> 3rd ed., pages 122-126. Knuth cites his source as "The polar
> method", G. E. P. Box, M. E. Muller, and G. Marsaglia,
> _Annals_Math,_Stat._ 29 (1958), 610-611.
>
> crosstabN(text sql)
> - returns a set of row_name plus N category value columns
> - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> but you can create additional crosstab functions per directions
> in the README.
>
> crosstabN example usage
>
> create table ct(id serial, rowclass text, rowid text, attribute text,
> value text);
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att1','val1');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att2','val2');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att3','val3');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att4','val4');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att1','val5');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att2','val6');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att3','val7');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att4','val8');
>
> select * from crosstab3(
> 'select rowid, attribute, value
> from ct
> where rowclass = ''group1''
> and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
>
> row_name | category_1 | category_2 | category_3
> ----------+------------+------------+------------
> test1 | val2 | val3 |
> test2 | val6 | val7 |
> (2 rows)
>
>
> Note that this patch depends on the guc_and_tablefunc patch I sent in a
> few minutes ago.
>
> Please apply if no objections.
>
> Thanks,
>
> Joe

> Index: contrib/tablefunc/Makefile
> ===================================================================
> RCS file: contrib/tablefunc/Makefile
> diff -N contrib/tablefunc/Makefile
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/Makefile 9 Jul 2002 03:34:24 -0000
> ***************
> *** 0 ****
> --- 1,9 ----
> + subdir = contrib/tablefunc
> + top_builddir = ../..
> + include $(top_builddir)/src/Makefile.global
> +
> + MODULES = tablefunc
> + DATA_built = tablefunc.sql
> + DOCS = README.tablefunc
> +
> + include $(top_srcdir)/contrib/contrib-global.mk
> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: contrib/tablefunc/README.tablefunc
> diff -N contrib/tablefunc/README.tablefunc
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/README.tablefunc 21 Jul 2002 02:50:53 -0000
> ***************
> *** 0 ****
> --- 1,272 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + Version 0.1 (20 July, 2002):
> + First release
> +
> + Release Notes:
> +
> + Version 0.1
> + - initial release
> +
> + Installation:
> + Place these files in a directory called 'tablefunc' under 'contrib' in the
> + PostgreSQL source tree. Then run:
> +
> + make
> + make install
> +
> + You can use tablefunc.sql to create the functions in your database of choice, e.g.
> +
> + psql -U postgres template1 < tablefunc.sql
> +
> + installs following functions into database template1:
> +
> + show_all_settings()
> + - returns the same information as SHOW ALL, but as a query result
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> + - returns a set of normally distributed float8 values
> +
> + crosstabN(text sql)
> + - returns a set of row_name plus N category value columns
> + - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> + but you can create additional crosstab functions per the instructions
> + in the documentation below.
> +
> + Documentation
> + ==================================================================
> + Name
> +
> + show_all_settings() - returns the same information as SHOW ALL,
> + but as a query result.
> +
> + Synopsis
> +
> + show_all_settings()
> +
> + Inputs
> +
> + none
> +
> + Outputs
> +
> + Returns setof tablefunc_config_settings which is defined by:
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + Example usage
> +
> + test=# select * from show_all_settings();
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> +
> + ==================================================================
> + Name
> +
> + normal_rand(int, float8, float8, int) - returns a set of normally
> + distributed float8 values
> +
> + Synopsis
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> +
> + Inputs
> +
> + numvals
> + the number of random values to be returned from the function
> +
> + mean
> + the mean of the normal distribution of values
> +
> + stddev
> + the standard deviation of the normal distribution of values
> +
> + seed
> + a seed value for the pseudo-random number generator
> +
> + Outputs
> +
> + Returns setof float8, where the returned set of random values are normally
> + distributed (Gaussian distribution)
> +
> + Example usage
> +
> + test=# SELECT * FROM
> + test=# normal_rand(1000, 5, 3, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> + normal_rand
> + ----------------------
> + 1.56556322244898
> + 9.10040991424657
> + 5.36957140345079
> + -0.369151492880995
> + 0.283600703686639
> + .
> + .
> + .
> + 4.82992125404908
> + 9.71308014517282
> + 2.49639286969028
> + (1000 rows)
> +
> + Returns 1000 values with a mean of 5 and a standard deviation of 3.
> +
> + ==================================================================
> + Name
> +
> + crosstabN(text) - returns a set of row_name plus N category value columns
> +
> + Synopsis
> +
> + crosstabN(text sql)
> +
> + Inputs
> +
> + sql
> +
> + A SQL statement which produces the source set of data. The SQL statement
> + must return one row_name column, one category column, and one value
> + column.
> +
> + e.g. provided sql must produce a set something like:
> +
> + row_name cat value
> + ----------+-------+-------
> + row1 cat1 val1
> + row1 cat2 val2
> + row1 cat3 val3
> + row1 cat4 val4
> + row2 cat1 val5
> + row2 cat2 val6
> + row2 cat3 val7
> + row2 cat4 val8
> +
> + Outputs
> +
> + Returns setof tablefunc_crosstab_N, which is defined by:
> +
> + CREATE VIEW tablefunc_crosstab_N AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + .
> + .
> + .
> + ''::TEXT AS category_N;
> +
> + for the default installed functions, where N is 2, 3, or 4.
> +
> + e.g. the provided crosstab2 function produces a set something like:
> + <== values columns ==>
> + row_name category_1 category_2
> + ---------+------------+------------
> + row1 val1 val2
> + row2 val5 val6
> +
> + Notes
> +
> + 1. The sql result must be ordered by 1,2.
> +
> + 2. The number of values columns depends on the tuple description
> + of the function's declared return type.
> +
> + 3. Missing values (i.e. not enough adjacent rows of same row_name to
> + fill the number of result values columns) are filled in with nulls.
> +
> + 4. Extra values (i.e. too many adjacent rows of same row_name to fill
> + the number of result values columns) are skipped.
> +
> + 5. Rows with all nulls in the values columns are skipped.
> +
> + 6. The installed defaults are for illustration purposes. You
> + can create your own return types and functions based on the
> + crosstab() function of the installed library.
> +
> + The return type must have a first column that matches the data
> + type of the sql set used as its source. The subsequent category
> + columns must have the same data type as the value column of the
> + sql result set.
> +
> + Create a VIEW to define your return type, similar to the VIEWS
> + in the provided installation script. Then define a unique function
> + name accepting one text parameter and returning setof your_view_name.
> + For example, if your source data produces row_names that are TEXT,
> + and values that are FLOAT8, and you want 5 category columns:
> +
> + CREATE VIEW my_crosstab_float8_5_cols AS
> + SELECT
> + ''::TEXT AS row_name,
> + 0::FLOAT8 AS category_1,
> + 0::FLOAT8 AS category_2,
> + 0::FLOAT8 AS category_3,
> + 0::FLOAT8 AS category_4,
> + 0::FLOAT8 AS category_5;
> +
> + CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
> + RETURNS setof my_crosstab_float8_5_cols
> + AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + Example usage
> +
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> +
> + select * from crosstab3(
> + 'select rowid, attribute, value
> + from ct
> + where rowclass = ''group1''
> + and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + row_name | category_1 | category_2 | category_3
> + ----------+------------+------------+------------
> + test1 | val2 | val3 |
> + test2 | val6 | val7 |
> + (2 rows)
> +
> + ==================================================================
> + -- Joe Conway
> +
> Index: contrib/tablefunc/tablefunc-test.sql
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc-test.sql
> diff -N contrib/tablefunc/tablefunc-test.sql
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc-test.sql 21 Jul 2002 03:01:13 -0000
> ***************
> *** 0 ****
> --- 1,47 ----
> + --
> + -- show_all_settings()
> + --
> + SELECT * FROM show_all_settings();
> +
> + --
> + -- normal_rand()
> + --
> + SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> +
> + --
> + -- crosstab()
> + --
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> +
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att1','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att2','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att3','val6');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> +
> +
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.c
> diff -N contrib/tablefunc/tablefunc.c
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.c 21 Jul 2002 01:02:57 -0000
> ***************
> *** 0 ****
> --- 1,664 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + #include <stdlib.h>
> + #include <math.h>
> +
> + #include "postgres.h"
> +
> + #include "fmgr.h"
> + #include "funcapi.h"
> + #include "executor/spi.h"
> + #include "utils/builtins.h"
> + #include "utils/guc.h"
> +
> + #include "tablefunc.h"
> +
> + static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> + static void get_normal_pair(float8 *x1, float8 *x2);
> +
> + typedef struct
> + {
> + float8 mean; /* mean of the distribution */
> + float8 stddev; /* stddev of the distribution */
> + float8 carry_val; /* hold second generated value */
> + bool use_carry; /* use second generated value */
> + } normal_rand_fctx;
> +
> + typedef struct
> + {
> + SPITupleTable *spi_tuptable; /* sql results from user query */
> + char *lastrowid; /* rowid of the last tuple sent */
> + } crosstab_fctx;
> +
> + #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> + #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> + #define xpfree(var_) \
> + do { \
> + if (var_ != NULL) \
> + { \
> + pfree(var_); \
> + var_ = NULL; \
> + } \
> + } while (0)
> +
> + /*
> + * show_all_settings - equiv to SHOW ALL command but implemented as
> + * a Table Function.
> + */
> + PG_FUNCTION_INFO_V1(show_all_settings);
> + Datum
> + show_all_settings(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + Oid foid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = foidGetTypeId(foid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = GetNumConfigOptions();
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + attinmeta = funcctx->attinmeta;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + char **values;
> + char *varname;
> + char *varval;
> + bool noshow;
> + HeapTuple tuple;
> + Datum result;
> +
> + /*
> + * Get the next visible GUC variable name and value
> + */
> + do
> + {
> + varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
> + if (noshow)
> + {
> + /* varval is a palloc'd copy, so free it */
> + xpfree(varval);
> +
> + /* bump the counter and get the next config setting */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* make sure we haven't gone too far now */
> + if (call_cntr >= max_calls)
> + SRF_RETURN_DONE(funcctx);
> + }
> + } while (noshow);
> +
> + /*
> + * Prepare a values array for storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" functions.
> + */
> + values = (char **) palloc(2 * sizeof(char *));
> + values[0] = pstrdup(varname);
> + values[1] = varval; /* varval is already a palloc'd copy */
> +
> + /* build a tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + xpfree(values[0]);
> + xpfree(values[1]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * normal_rand - return requested number of random values
> + * with a Gaussian (Normal) distribution.
> + *
> + * inputs are int numvals, float8 lower_bound, and float8 upper_bound
> + * returns float8
> + */
> + PG_FUNCTION_INFO_V1(normal_rand);
> + Datum
> + normal_rand(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + normal_rand_fctx *fctx;
> + float8 mean;
> + float8 stddev;
> + float8 carry_val;
> + bool use_carry;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /* allocate memory for user context */
> + fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
> +
> + /*
> + * Use fctx to keep track of upper and lower bounds
> + * from call to call. It will also be used to carry over
> + * the spare value we get from the Box-Muller algorithm
> + * so that we only actually calculate a new value every
> + * other call.
> + */
> + fctx->mean = PG_GETARG_FLOAT8(1);
> + fctx->stddev = PG_GETARG_FLOAT8(2);
> + fctx->carry_val = 0;
> + fctx->use_carry = false;
> +
> + funcctx->user_fctx = fctx;
> +
> + /*
> + * we might actually get passed a negative number, but for this
> + * purpose it doesn't matter, just cast it as an unsigned value
> + */
> + srandom(PG_GETARG_UINT32(3));
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + fctx = funcctx->user_fctx;
> + mean = fctx->mean;
> + stddev = fctx->stddev;
> + carry_val = fctx->carry_val;
> + use_carry = fctx->use_carry;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + float8 result;
> +
> + if(use_carry)
> + {
> + /*
> + * reset use_carry and use second value obtained on last pass
> + */
> + fctx->use_carry = false;
> + result = carry_val;
> + }
> + else
> + {
> + float8 normval_1;
> + float8 normval_2;
> +
> + /* Get the next two normal values */
> + get_normal_pair(&normval_1, &normval_2);
> +
> + /* use the first */
> + result = mean + (stddev * normval_1);
> +
> + /* and save the second */
> + fctx->carry_val = mean + (stddev * normval_2);
> + fctx->use_carry = true;
> + }
> +
> + /* send the result */
> + SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * get_normal_pair()
> + * Assigns normally distributed (Gaussian) values to a pair of provided
> + * parameters, with mean 0, standard deviation 1.
> + *
> + * This routine implements Algorithm P (Polar method for normal deviates)
> + * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
> + * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
> + * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
> + *
> + */
> + static void
> + get_normal_pair(float8 *x1, float8 *x2)
> + {
> + float8 u1, u2, v1, v2, s;
> +
> + for(;;)
> + {
> + u1 = (float8) random() / (float8) RAND_MAX;
> + u2 = (float8) random() / (float8) RAND_MAX;
> +
> + v1 = (2.0 * u1) - 1.0;
> + v2 = (2.0 * u2) - 1.0;
> +
> + s = pow(v1, 2) + pow(v2, 2);
> +
> + if (s >= 1.0)
> + continue;
> +
> + if (s == 0)
> + {
> + *x1 = 0;
> + *x2 = 0;
> + }
> + else
> + {
> + *x1 = v1 * sqrt((-2.0 * log(s)) / s);
> + *x2 = v2 * sqrt((-2.0 * log(s)) / s);
> + }
> +
> + return;
> + }
> + }
> +
> + /*
> + * crosstab - create a crosstab of rowids and values columns from a
> + * SQL statement returning one rowid column, one category column,
> + * and one value column.
> + *
> + * e.g. given sql which produces:
> + *
> + * rowid cat value
> + * ------+-------+-------
> + * row1 cat1 val1
> + * row1 cat2 val2
> + * row1 cat3 val3
> + * row1 cat4 val4
> + * row2 cat1 val5
> + * row2 cat2 val6
> + * row2 cat3 val7
> + * row2 cat4 val8
> + *
> + * crosstab returns:
> + * <===== values columns =====>
> + * rowid cat1 cat2 cat3 cat4
> + * ------+-------+-------+-------+-------
> + * row1 val1 val2 val3 val4
> + * row2 val5 val6 val7 val8
> + *
> + * NOTES:
> + * 1. SQL result must be ordered by 1,2.
> + * 2. The number of values columns depends on the tuple description
> + * of the function's declared return type.
> + * 2. Missing values (i.e. not enough adjacent rows of same rowid to
> + * fill the number of result values columns) are filled in with nulls.
> + * 3. Extra values (i.e. too many adjacent rows of same rowid to fill
> + * the number of result values columns) are skipped.
> + * 4. Rows with all nulls in the values columns are skipped.
> + */
> + PG_FUNCTION_INFO_V1(crosstab);
> + Datum
> + crosstab(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc ret_tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> + SPITupleTable *spi_tuptable;
> + TupleDesc spi_tupdesc;
> + char *lastrowid;
> + crosstab_fctx *fctx;
> + int i;
> + int num_categories;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + char *sql = GET_STR(PG_GETARG_TEXT_P(0));
> + Oid foid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> + TupleDesc tupdesc;
> + int ret;
> + int proc;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = foidGetTypeId(foid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* Connect to SPI manager */
> + if ((ret = SPI_connect()) < 0)
> + elog(ERROR, "crosstab: SPI_connect returned %d", ret);
> +
> + /* Retrieve the desired rows */
> + ret = SPI_exec(sql, 0);
> + proc = SPI_processed;
> +
> + /* Check for qualifying tuples */
> + if ((ret == SPI_OK_SELECT) && (proc > 0))
> + {
> + spi_tuptable = SPI_tuptable;
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /*
> + * The provided SQL query must always return three columns.
> + *
> + * 1. rowid the label or identifier for each row in the final
> + * result
> + * 2. category the label or identifier for each column in the
> + * final result
> + * 3. values the value for each column in the final result
> + */
> + if (spi_tupdesc->natts != 3)
> + elog(ERROR, "crosstab: provided SQL must return 3 columns;"
> + " a rowid, a category, and a values column");
> +
> + /*
> + * Check that return tupdesc is compatible with the one we got
> + * from ret_relname, at least based on number and type of
> + * attributes
> + */
> + if (!compatTupleDescs(tupdesc, spi_tupdesc))
> + elog(ERROR, "crosstab: return and sql tuple descriptions are"
> + " incompatible");
> +
> + /* allocate memory for user context */
> + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
> +
> + /*
> + * OK, we have data, and it seems to be valid, so save it
> + * for use across calls
> + */
> + fctx->spi_tuptable = spi_tuptable;
> + fctx->lastrowid = NULL;
> + funcctx->user_fctx = fctx;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = proc;
> + }
> + else
> + {
> + /* no qualifying tuples */
> + funcctx->max_calls = 0;
> + }
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + /*
> + * initialize per-call variables
> + */
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> +
> + /* return slot for our tuple */
> + slot = funcctx->slot;
> +
> + /* user context info */
> + fctx = (crosstab_fctx *) funcctx->user_fctx;
> + lastrowid = fctx->lastrowid;
> + spi_tuptable = fctx->spi_tuptable;
> +
> + /* the sql tuple */
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /* attribute return type and return tuple description */
> + attinmeta = funcctx->attinmeta;
> + ret_tupdesc = attinmeta->tupdesc;
> +
> + /* the return tuple always must have 1 rowid + num_categories columns */
> + num_categories = ret_tupdesc->natts - 1;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + HeapTuple tuple;
> + Datum result;
> + char **values;
> + bool allnulls = true;
> +
> + while (true)
> + {
> + /* allocate space */
> + values = (char **) palloc((1 + num_categories) * sizeof(char *));
> +
> + /* and make sure it's clear */
> + memset(values, '\0', (1 + num_categories) * sizeof(char *));
> +
> + /*
> + * now loop through the sql results and assign each value
> + * in sequence to the next category
> + */
> + for (i = 0; i < num_categories; i++)
> + {
> + HeapTuple spi_tuple;
> + char *rowid;
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + break;
> +
> + /* get the next sql result tuple */
> + spi_tuple = spi_tuptable->vals[call_cntr];
> +
> + /* get the rowid from the current sql result tuple */
> + rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
> +
> + /*
> + * If this is the first pass through the values for this rowid
> + * set it, otherwise make sure it hasn't changed on us. Also
> + * check to see if the rowid is the same as that of the last
> + * tuple sent -- if so, skip this tuple entirely
> + */
> + if (i == 0)
> + values[0] = pstrdup(rowid);
> +
> + if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
> + {
> + if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
> + break;
> + else if (allnulls == true)
> + allnulls = false;
> +
> + /*
> + * Get the next category item value, which is alway attribute
> + * number three.
> + *
> + * Be careful to sssign the value to the array index based
> + * on which category we are presently processing.
> + */
> + values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> +
> + /*
> + * increment the counter since we consume a row
> + * for each category, but not for last pass
> + * because the API will do that for us
> + */
> + if (i < (num_categories - 1))
> + call_cntr = ++funcctx->call_cntr;
> + }
> + else
> + {
> + /*
> + * We'll fill in NULLs for the missing values,
> + * but we need to decrement the counter since
> + * this sql result row doesn't belong to the current
> + * output tuple.
> + */
> + call_cntr = --funcctx->call_cntr;
> + break;
> + }
> +
> + if (rowid != NULL)
> + xpfree(rowid);
> + }
> +
> + xpfree(fctx->lastrowid);
> +
> + if (values[0] != NULL)
> + lastrowid = fctx->lastrowid = pstrdup(values[0]);
> +
> + if (!allnulls)
> + {
> + /* build the tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + for (i = 0; i < num_categories + 1; i++)
> + if (values[i] != NULL)
> + xpfree(values[i]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + /*
> + * Skipping this tuple entirely, but we need to advance
> + * the counter like the API would if we had returned
> + * one.
> + */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* we'll start over at the top */
> + xpfree(values);
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + }
> + }
> + else /* do when there is no more left */
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * Check if two tupdescs match in type of attributes
> + */
> + static bool
> + compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
> + {
> + int i;
> + Form_pg_attribute ret_attr;
> + Oid ret_atttypid;
> + Form_pg_attribute sql_attr;
> + Oid sql_atttypid;
> +
> + /* check the rowid types match */
> + ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
> + sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
> + if (ret_atttypid != sql_atttypid)
> + elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match"
> + " return rowid datatype");
> +
> + /*
> + * - attribute [1] of the sql tuple is the category;
> + * no need to check it
> + * - attribute [2] of the sql tuple should match
> + * attributes [1] to [natts] of the return tuple
> + */
> + sql_attr = sql_tupdesc->attrs[2];
> + for (i = 1; i < ret_tupdesc->natts; i++)
> + {
> + ret_attr = ret_tupdesc->attrs[i];
> +
> + if (ret_attr->atttypid != sql_attr->atttypid)
> + return false;
> + }
> +
> + /* OK, the two tupdescs are compatible for our purposes */
> + return true;
> + }
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.h
> diff -N contrib/tablefunc/tablefunc.h
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.h 19 Jul 2002 04:46:28 -0000
> ***************
> *** 0 ****
> --- 1,39 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> +
> + #ifndef TABLEFUNC_H
> + #define TABLEFUNC_H
> +
> + /*
> + * External declarations
> + */
> + extern Datum show_all_settings(PG_FUNCTION_ARGS);
> + extern Datum normal_rand(PG_FUNCTION_ARGS);
> + extern Datum crosstab(PG_FUNCTION_ARGS);
> +
> + #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.sql.in
> diff -N contrib/tablefunc/tablefunc.sql.in
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.sql.in 21 Jul 2002 01:19:53 -0000
> ***************
> *** 0 ****
> --- 1,46 ----
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + CREATE OR REPLACE FUNCTION show_all_settings()
> + RETURNS setof tablefunc_config_settings
> + AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
> + RETURNS setof float8
> + AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
> +
> + CREATE VIEW tablefunc_crosstab_2 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2;
> +
> + CREATE VIEW tablefunc_crosstab_3 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3;
> +
> + CREATE VIEW tablefunc_crosstab_4 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3,
> + ''::TEXT AS category_4;
> +
> + CREATE OR REPLACE FUNCTION crosstab2(text)
> + RETURNS setof tablefunc_crosstab_2
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab3(text)
> + RETURNS setof tablefunc_crosstab_3
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab4(text)
> + RETURNS setof tablefunc_crosstab_4
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Joe Conway wrote:
> > Here (finally ;-)) is a doc patch covering the Table Function C API. It
> > reflects the changes in the tablefunc-fix patch that I sent in the other
> > day. It also refers to "see contrib/tablefunc for more examples", which
> > is next on my list of things to finish and submit.
>
> As mentioned above, here is my contrib/tablefunc patch. It includes
> three functions which exercise the tablefunc API.
>
> show_all_settings()
> - returns the same information as SHOW ALL, but as a query result
>
> normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> - returns a set of normally distributed float8 values
> - This routine implements Algorithm P (Polar method for normal
> deviates) from Knuth's _The_Art_of_Computer_Programming_, Volume 2,
> 3rd ed., pages 122-126. Knuth cites his source as "The polar
> method", G. E. P. Box, M. E. Muller, and G. Marsaglia,
> _Annals_Math,_Stat._ 29 (1958), 610-611.
>
> crosstabN(text sql)
> - returns a set of row_name plus N category value columns
> - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> but you can create additional crosstab functions per directions
> in the README.
>
> crosstabN example usage
>
> create table ct(id serial, rowclass text, rowid text, attribute text,
> value text);
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att1','val1');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att2','val2');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att3','val3');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test1','att4','val4');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att1','val5');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att2','val6');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att3','val7');
> insert into ct(rowclass, rowid, attribute, value)
> values('group1','test2','att4','val8');
>
> select * from crosstab3(
> 'select rowid, attribute, value
> from ct
> where rowclass = ''group1''
> and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
>
> row_name | category_1 | category_2 | category_3
> ----------+------------+------------+------------
> test1 | val2 | val3 |
> test2 | val6 | val7 |
> (2 rows)
>
>
> Note that this patch depends on the guc_and_tablefunc patch I sent in a
> few minutes ago.
>
> Please apply if no objections.
>
> Thanks,
>
> Joe

> Index: contrib/tablefunc/Makefile
> ===================================================================
> RCS file: contrib/tablefunc/Makefile
> diff -N contrib/tablefunc/Makefile
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/Makefile 9 Jul 2002 03:34:24 -0000
> ***************
> *** 0 ****
> --- 1,9 ----
> + subdir = contrib/tablefunc
> + top_builddir = ../..
> + include $(top_builddir)/src/Makefile.global
> +
> + MODULES = tablefunc
> + DATA_built = tablefunc.sql
> + DOCS = README.tablefunc
> +
> + include $(top_srcdir)/contrib/contrib-global.mk
> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: contrib/tablefunc/README.tablefunc
> diff -N contrib/tablefunc/README.tablefunc
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/README.tablefunc 21 Jul 2002 02:50:53 -0000
> ***************
> *** 0 ****
> --- 1,272 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + Version 0.1 (20 July, 2002):
> + First release
> +
> + Release Notes:
> +
> + Version 0.1
> + - initial release
> +
> + Installation:
> + Place these files in a directory called 'tablefunc' under 'contrib' in the
> + PostgreSQL source tree. Then run:
> +
> + make
> + make install
> +
> + You can use tablefunc.sql to create the functions in your database of choice, e.g.
> +
> + psql -U postgres template1 < tablefunc.sql
> +
> + installs following functions into database template1:
> +
> + show_all_settings()
> + - returns the same information as SHOW ALL, but as a query result
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> + - returns a set of normally distributed float8 values
> +
> + crosstabN(text sql)
> + - returns a set of row_name plus N category value columns
> + - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> + but you can create additional crosstab functions per the instructions
> + in the documentation below.
> +
> + Documentation
> + ==================================================================
> + Name
> +
> + show_all_settings() - returns the same information as SHOW ALL,
> + but as a query result.
> +
> + Synopsis
> +
> + show_all_settings()
> +
> + Inputs
> +
> + none
> +
> + Outputs
> +
> + Returns setof tablefunc_config_settings which is defined by:
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + Example usage
> +
> + test=# select * from show_all_settings();
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> +
> + ==================================================================
> + Name
> +
> + normal_rand(int, float8, float8, int) - returns a set of normally
> + distributed float8 values
> +
> + Synopsis
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> +
> + Inputs
> +
> + numvals
> + the number of random values to be returned from the function
> +
> + mean
> + the mean of the normal distribution of values
> +
> + stddev
> + the standard deviation of the normal distribution of values
> +
> + seed
> + a seed value for the pseudo-random number generator
> +
> + Outputs
> +
> + Returns setof float8, where the returned set of random values are normally
> + distributed (Gaussian distribution)
> +
> + Example usage
> +
> + test=# SELECT * FROM
> + test=# normal_rand(1000, 5, 3, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> + normal_rand
> + ----------------------
> + 1.56556322244898
> + 9.10040991424657
> + 5.36957140345079
> + -0.369151492880995
> + 0.283600703686639
> + .
> + .
> + .
> + 4.82992125404908
> + 9.71308014517282
> + 2.49639286969028
> + (1000 rows)
> +
> + Returns 1000 values with a mean of 5 and a standard deviation of 3.
> +
> + ==================================================================
> + Name
> +
> + crosstabN(text) - returns a set of row_name plus N category value columns
> +
> + Synopsis
> +
> + crosstabN(text sql)
> +
> + Inputs
> +
> + sql
> +
> + A SQL statement which produces the source set of data. The SQL statement
> + must return one row_name column, one category column, and one value
> + column.
> +
> + e.g. provided sql must produce a set something like:
> +
> + row_name cat value
> + ----------+-------+-------
> + row1 cat1 val1
> + row1 cat2 val2
> + row1 cat3 val3
> + row1 cat4 val4
> + row2 cat1 val5
> + row2 cat2 val6
> + row2 cat3 val7
> + row2 cat4 val8
> +
> + Outputs
> +
> + Returns setof tablefunc_crosstab_N, which is defined by:
> +
> + CREATE VIEW tablefunc_crosstab_N AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + .
> + .
> + .
> + ''::TEXT AS category_N;
> +
> + for the default installed functions, where N is 2, 3, or 4.
> +
> + e.g. the provided crosstab2 function produces a set something like:
> + <== values columns ==>
> + row_name category_1 category_2
> + ---------+------------+------------
> + row1 val1 val2
> + row2 val5 val6
> +
> + Notes
> +
> + 1. The sql result must be ordered by 1,2.
> +
> + 2. The number of values columns depends on the tuple description
> + of the function's declared return type.
> +
> + 3. Missing values (i.e. not enough adjacent rows of same row_name to
> + fill the number of result values columns) are filled in with nulls.
> +
> + 4. Extra values (i.e. too many adjacent rows of same row_name to fill
> + the number of result values columns) are skipped.
> +
> + 5. Rows with all nulls in the values columns are skipped.
> +
> + 6. The installed defaults are for illustration purposes. You
> + can create your own return types and functions based on the
> + crosstab() function of the installed library.
> +
> + The return type must have a first column that matches the data
> + type of the sql set used as its source. The subsequent category
> + columns must have the same data type as the value column of the
> + sql result set.
> +
> + Create a VIEW to define your return type, similar to the VIEWS
> + in the provided installation script. Then define a unique function
> + name accepting one text parameter and returning setof your_view_name.
> + For example, if your source data produces row_names that are TEXT,
> + and values that are FLOAT8, and you want 5 category columns:
> +
> + CREATE VIEW my_crosstab_float8_5_cols AS
> + SELECT
> + ''::TEXT AS row_name,
> + 0::FLOAT8 AS category_1,
> + 0::FLOAT8 AS category_2,
> + 0::FLOAT8 AS category_3,
> + 0::FLOAT8 AS category_4,
> + 0::FLOAT8 AS category_5;
> +
> + CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
> + RETURNS setof my_crosstab_float8_5_cols
> + AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + Example usage
> +
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> +
> + select * from crosstab3(
> + 'select rowid, attribute, value
> + from ct
> + where rowclass = ''group1''
> + and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + row_name | category_1 | category_2 | category_3
> + ----------+------------+------------+------------
> + test1 | val2 | val3 |
> + test2 | val6 | val7 |
> + (2 rows)
> +
> + ==================================================================
> + -- Joe Conway
> +
> Index: contrib/tablefunc/tablefunc-test.sql
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc-test.sql
> diff -N contrib/tablefunc/tablefunc-test.sql
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc-test.sql 21 Jul 2002 03:01:13 -0000
> ***************
> *** 0 ****
> --- 1,47 ----
> + --
> + -- show_all_settings()
> + --
> + SELECT * FROM show_all_settings();
> +
> + --
> + -- normal_rand()
> + --
> + SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> +
> + --
> + -- crosstab()
> + --
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> +
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att1','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att2','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att3','val6');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> +
> +
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.c
> diff -N contrib/tablefunc/tablefunc.c
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.c 21 Jul 2002 01:02:57 -0000
> ***************
> *** 0 ****
> --- 1,664 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + #include <stdlib.h>
> + #include <math.h>
> +
> + #include "postgres.h"
> +
> + #include "fmgr.h"
> + #include "funcapi.h"
> + #include "executor/spi.h"
> + #include "utils/builtins.h"
> + #include "utils/guc.h"
> +
> + #include "tablefunc.h"
> +
> + static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> + static void get_normal_pair(float8 *x1, float8 *x2);
> +
> + typedef struct
> + {
> + float8 mean; /* mean of the distribution */
> + float8 stddev; /* stddev of the distribution */
> + float8 carry_val; /* hold second generated value */
> + bool use_carry; /* use second generated value */
> + } normal_rand_fctx;
> +
> + typedef struct
> + {
> + SPITupleTable *spi_tuptable; /* sql results from user query */
> + char *lastrowid; /* rowid of the last tuple sent */
> + } crosstab_fctx;
> +
> + #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> + #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> + #define xpfree(var_) \
> + do { \
> + if (var_ != NULL) \
> + { \
> + pfree(var_); \
> + var_ = NULL; \
> + } \
> + } while (0)
> +
> + /*
> + * show_all_settings - equiv to SHOW ALL command but implemented as
> + * a Table Function.
> + */
> + PG_FUNCTION_INFO_V1(show_all_settings);
> + Datum
> + show_all_settings(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + Oid foid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = foidGetTypeId(foid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = GetNumConfigOptions();
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + attinmeta = funcctx->attinmeta;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + char **values;
> + char *varname;
> + char *varval;
> + bool noshow;
> + HeapTuple tuple;
> + Datum result;
> +
> + /*
> + * Get the next visible GUC variable name and value
> + */
> + do
> + {
> + varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
> + if (noshow)
> + {
> + /* varval is a palloc'd copy, so free it */
> + xpfree(varval);
> +
> + /* bump the counter and get the next config setting */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* make sure we haven't gone too far now */
> + if (call_cntr >= max_calls)
> + SRF_RETURN_DONE(funcctx);
> + }
> + } while (noshow);
> +
> + /*
> + * Prepare a values array for storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" functions.
> + */
> + values = (char **) palloc(2 * sizeof(char *));
> + values[0] = pstrdup(varname);
> + values[1] = varval; /* varval is already a palloc'd copy */
> +
> + /* build a tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + xpfree(values[0]);
> + xpfree(values[1]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * normal_rand - return requested number of random values
> + * with a Gaussian (Normal) distribution.
> + *
> + * inputs are int numvals, float8 lower_bound, and float8 upper_bound
> + * returns float8
> + */
> + PG_FUNCTION_INFO_V1(normal_rand);
> + Datum
> + normal_rand(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + normal_rand_fctx *fctx;
> + float8 mean;
> + float8 stddev;
> + float8 carry_val;
> + bool use_carry;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /* allocate memory for user context */
> + fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
> +
> + /*
> + * Use fctx to keep track of upper and lower bounds
> + * from call to call. It will also be used to carry over
> + * the spare value we get from the Box-Muller algorithm
> + * so that we only actually calculate a new value every
> + * other call.
> + */
> + fctx->mean = PG_GETARG_FLOAT8(1);
> + fctx->stddev = PG_GETARG_FLOAT8(2);
> + fctx->carry_val = 0;
> + fctx->use_carry = false;
> +
> + funcctx->user_fctx = fctx;
> +
> + /*
> + * we might actually get passed a negative number, but for this
> + * purpose it doesn't matter, just cast it as an unsigned value
> + */
> + srandom(PG_GETARG_UINT32(3));
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + fctx = funcctx->user_fctx;
> + mean = fctx->mean;
> + stddev = fctx->stddev;
> + carry_val = fctx->carry_val;
> + use_carry = fctx->use_carry;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + float8 result;
> +
> + if(use_carry)
> + {
> + /*
> + * reset use_carry and use second value obtained on last pass
> + */
> + fctx->use_carry = false;
> + result = carry_val;
> + }
> + else
> + {
> + float8 normval_1;
> + float8 normval_2;
> +
> + /* Get the next two normal values */
> + get_normal_pair(&normval_1, &normval_2);
> +
> + /* use the first */
> + result = mean + (stddev * normval_1);
> +
> + /* and save the second */
> + fctx->carry_val = mean + (stddev * normval_2);
> + fctx->use_carry = true;
> + }
> +
> + /* send the result */
> + SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * get_normal_pair()
> + * Assigns normally distributed (Gaussian) values to a pair of provided
> + * parameters, with mean 0, standard deviation 1.
> + *
> + * This routine implements Algorithm P (Polar method for normal deviates)
> + * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
> + * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
> + * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
> + *
> + */
> + static void
> + get_normal_pair(float8 *x1, float8 *x2)
> + {
> + float8 u1, u2, v1, v2, s;
> +
> + for(;;)
> + {
> + u1 = (float8) random() / (float8) RAND_MAX;
> + u2 = (float8) random() / (float8) RAND_MAX;
> +
> + v1 = (2.0 * u1) - 1.0;
> + v2 = (2.0 * u2) - 1.0;
> +
> + s = pow(v1, 2) + pow(v2, 2);
> +
> + if (s >= 1.0)
> + continue;
> +
> + if (s == 0)
> + {
> + *x1 = 0;
> + *x2 = 0;
> + }
> + else
> + {
> + *x1 = v1 * sqrt((-2.0 * log(s)) / s);
> + *x2 = v2 * sqrt((-2.0 * log(s)) / s);
> + }
> +
> + return;
> + }
> + }
> +
> + /*
> + * crosstab - create a crosstab of rowids and values columns from a
> + * SQL statement returning one rowid column, one category column,
> + * and one value column.
> + *
> + * e.g. given sql which produces:
> + *
> + * rowid cat value
> + * ------+-------+-------
> + * row1 cat1 val1
> + * row1 cat2 val2
> + * row1 cat3 val3
> + * row1 cat4 val4
> + * row2 cat1 val5
> + * row2 cat2 val6
> + * row2 cat3 val7
> + * row2 cat4 val8
> + *
> + * crosstab returns:
> + * <===== values columns =====>
> + * rowid cat1 cat2 cat3 cat4
> + * ------+-------+-------+-------+-------
> + * row1 val1 val2 val3 val4
> + * row2 val5 val6 val7 val8
> + *
> + * NOTES:
> + * 1. SQL result must be ordered by 1,2.
> + * 2. The number of values columns depends on the tuple description
> + * of the function's declared return type.
> + * 2. Missing values (i.e. not enough adjacent rows of same rowid to
> + * fill the number of result values columns) are filled in with nulls.
> + * 3. Extra values (i.e. too many adjacent rows of same rowid to fill
> + * the number of result values columns) are skipped.
> + * 4. Rows with all nulls in the values columns are skipped.
> + */
> + PG_FUNCTION_INFO_V1(crosstab);
> + Datum
> + crosstab(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc ret_tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> + SPITupleTable *spi_tuptable;
> + TupleDesc spi_tupdesc;
> + char *lastrowid;
> + crosstab_fctx *fctx;
> + int i;
> + int num_categories;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + char *sql = GET_STR(PG_GETARG_TEXT_P(0));
> + Oid foid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> + TupleDesc tupdesc;
> + int ret;
> + int proc;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = foidGetTypeId(foid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* Connect to SPI manager */
> + if ((ret = SPI_connect()) < 0)
> + elog(ERROR, "crosstab: SPI_connect returned %d", ret);
> +
> + /* Retrieve the desired rows */
> + ret = SPI_exec(sql, 0);
> + proc = SPI_processed;
> +
> + /* Check for qualifying tuples */
> + if ((ret == SPI_OK_SELECT) && (proc > 0))
> + {
> + spi_tuptable = SPI_tuptable;
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /*
> + * The provided SQL query must always return three columns.
> + *
> + * 1. rowid the label or identifier for each row in the final
> + * result
> + * 2. category the label or identifier for each column in the
> + * final result
> + * 3. values the value for each column in the final result
> + */
> + if (spi_tupdesc->natts != 3)
> + elog(ERROR, "crosstab: provided SQL must return 3 columns;"
> + " a rowid, a category, and a values column");
> +
> + /*
> + * Check that return tupdesc is compatible with the one we got
> + * from ret_relname, at least based on number and type of
> + * attributes
> + */
> + if (!compatTupleDescs(tupdesc, spi_tupdesc))
> + elog(ERROR, "crosstab: return and sql tuple descriptions are"
> + " incompatible");
> +
> + /* allocate memory for user context */
> + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
> +
> + /*
> + * OK, we have data, and it seems to be valid, so save it
> + * for use across calls
> + */
> + fctx->spi_tuptable = spi_tuptable;
> + fctx->lastrowid = NULL;
> + funcctx->user_fctx = fctx;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = proc;
> + }
> + else
> + {
> + /* no qualifying tuples */
> + funcctx->max_calls = 0;
> + }
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + /*
> + * initialize per-call variables
> + */
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> +
> + /* return slot for our tuple */
> + slot = funcctx->slot;
> +
> + /* user context info */
> + fctx = (crosstab_fctx *) funcctx->user_fctx;
> + lastrowid = fctx->lastrowid;
> + spi_tuptable = fctx->spi_tuptable;
> +
> + /* the sql tuple */
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /* attribute return type and return tuple description */
> + attinmeta = funcctx->attinmeta;
> + ret_tupdesc = attinmeta->tupdesc;
> +
> + /* the return tuple always must have 1 rowid + num_categories columns */
> + num_categories = ret_tupdesc->natts - 1;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + HeapTuple tuple;
> + Datum result;
> + char **values;
> + bool allnulls = true;
> +
> + while (true)
> + {
> + /* allocate space */
> + values = (char **) palloc((1 + num_categories) * sizeof(char *));
> +
> + /* and make sure it's clear */
> + memset(values, '\0', (1 + num_categories) * sizeof(char *));
> +
> + /*
> + * now loop through the sql results and assign each value
> + * in sequence to the next category
> + */
> + for (i = 0; i < num_categories; i++)
> + {
> + HeapTuple spi_tuple;
> + char *rowid;
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + break;
> +
> + /* get the next sql result tuple */
> + spi_tuple = spi_tuptable->vals[call_cntr];
> +
> + /* get the rowid from the current sql result tuple */
> + rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
> +
> + /*
> + * If this is the first pass through the values for this rowid
> + * set it, otherwise make sure it hasn't changed on us. Also
> + * check to see if the rowid is the same as that of the last
> + * tuple sent -- if so, skip this tuple entirely
> + */
> + if (i == 0)
> + values[0] = pstrdup(rowid);
> +
> + if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
> + {
> + if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
> + break;
> + else if (allnulls == true)
> + allnulls = false;
> +
> + /*
> + * Get the next category item value, which is alway attribute
> + * number three.
> + *
> + * Be careful to sssign the value to the array index based
> + * on which category we are presently processing.
> + */
> + values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> +
> + /*
> + * increment the counter since we consume a row
> + * for each category, but not for last pass
> + * because the API will do that for us
> + */
> + if (i < (num_categories - 1))
> + call_cntr = ++funcctx->call_cntr;
> + }
> + else
> + {
> + /*
> + * We'll fill in NULLs for the missing values,
> + * but we need to decrement the counter since
> + * this sql result row doesn't belong to the current
> + * output tuple.
> + */
> + call_cntr = --funcctx->call_cntr;
> + break;
> + }
> +
> + if (rowid != NULL)
> + xpfree(rowid);
> + }
> +
> + xpfree(fctx->lastrowid);
> +
> + if (values[0] != NULL)
> + lastrowid = fctx->lastrowid = pstrdup(values[0]);
> +
> + if (!allnulls)
> + {
> + /* build the tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + for (i = 0; i < num_categories + 1; i++)
> + if (values[i] != NULL)
> + xpfree(values[i]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + /*
> + * Skipping this tuple entirely, but we need to advance
> + * the counter like the API would if we had returned
> + * one.
> + */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* we'll start over at the top */
> + xpfree(values);
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + }
> + }
> + else /* do when there is no more left */
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * Check if two tupdescs match in type of attributes
> + */
> + static bool
> + compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
> + {
> + int i;
> + Form_pg_attribute ret_attr;
> + Oid ret_atttypid;
> + Form_pg_attribute sql_attr;
> + Oid sql_atttypid;
> +
> + /* check the rowid types match */
> + ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
> + sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
> + if (ret_atttypid != sql_atttypid)
> + elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match"
> + " return rowid datatype");
> +
> + /*
> + * - attribute [1] of the sql tuple is the category;
> + * no need to check it
> + * - attribute [2] of the sql tuple should match
> + * attributes [1] to [natts] of the return tuple
> + */
> + sql_attr = sql_tupdesc->attrs[2];
> + for (i = 1; i < ret_tupdesc->natts; i++)
> + {
> + ret_attr = ret_tupdesc->attrs[i];
> +
> + if (ret_attr->atttypid != sql_attr->atttypid)
> + return false;
> + }
> +
> + /* OK, the two tupdescs are compatible for our purposes */
> + return true;
> + }
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.h
> diff -N contrib/tablefunc/tablefunc.h
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.h 19 Jul 2002 04:46:28 -0000
> ***************
> *** 0 ****
> --- 1,39 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> +
> + #ifndef TABLEFUNC_H
> + #define TABLEFUNC_H
> +
> + /*
> + * External declarations
> + */
> + extern Datum show_all_settings(PG_FUNCTION_ARGS);
> + extern Datum normal_rand(PG_FUNCTION_ARGS);
> + extern Datum crosstab(PG_FUNCTION_ARGS);
> +
> + #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.sql.in
> diff -N contrib/tablefunc/tablefunc.sql.in
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.sql.in 21 Jul 2002 01:19:53 -0000
> ***************
> *** 0 ****
> --- 1,46 ----
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + CREATE OR REPLACE FUNCTION show_all_settings()
> + RETURNS setof tablefunc_config_settings
> + AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
> + RETURNS setof float8
> + AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
> +
> + CREATE VIEW tablefunc_crosstab_2 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2;
> +
> + CREATE VIEW tablefunc_crosstab_3 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3;
> +
> + CREATE VIEW tablefunc_crosstab_4 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3,
> + ''::TEXT AS category_4;
> +
> + CREATE OR REPLACE FUNCTION crosstab2(text)
> + RETURNS setof tablefunc_crosstab_2
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab3(text)
> + RETURNS setof tablefunc_crosstab_3
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab4(text)
> + RETURNS setof tablefunc_crosstab_4
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026