Re: guc GetConfigOptionByNum and tablefunc API - minor changes - Mailing list pgsql-patches

Tom Lane wrote:
> Doesn't this duplicate get_func_rettype()?  (Which is more clearly
> named anyway; a function's type is a second-order concept IMHO...)

Here's a second try at all three patches. I removed the foidGetTypeId()
function from funcapi.c, and replaced references to it in
contrib/tablefunc with get_func_rettype().

The only change to funcapi now is a minor addition to the comments in
funcapi.h and the funcapi doc for the previously mentioned undocumented
function.

As always, thanks for the review.

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@joeconway.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''
orattribute = ''att3'') order by 1,2;'); 
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''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''
orattribute = ''att2'') order by 1,2;'); 
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''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 05:36:00 -0000
***************
*** 0 ****
--- 1,665 ----
+ /*
+  * tablefunc
+  *
+  * Sample to demonstrate C functions which return setof scalar
+  * and setof composite.
+  * Joe Conway <mail@joeconway.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 "utils/lsyscache.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         funcid = 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 = get_func_rettype(funcid);
+
+         /* 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         funcid = 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 = get_func_rettype(funcid);
+
+         /* 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@joeconway.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;
+
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.75
diff -c -r1.75 guc.c
*** src/backend/utils/misc/guc.c    20 Jul 2002 15:12:55 -0000    1.75
--- src/backend/utils/misc/guc.c    21 Jul 2002 01:36:12 -0000
***************
*** 2347,2358 ****
   * form of name.  Return value is palloc'd.
   */
  char *
! GetConfigOptionByNum(int varnum, const char **varname)
  {
!     struct config_generic *conf = guc_variables[varnum];

      if (varname)
          *varname = conf->name;

      return _ShowOption(conf);
  }
--- 2347,2366 ----
   * form of name.  Return value is palloc'd.
   */
  char *
! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow)
  {
!     struct config_generic *conf;
!
!     /* check requested variable number valid */
!     Assert((varnum >= 0) && (varnum < num_guc_variables));
!
!     conf = guc_variables[varnum];

      if (varname)
          *varname = conf->name;
+
+     if (noshow)
+         *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;

      return _ShowOption(conf);
  }
Index: src/include/funcapi.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/funcapi.h,v
retrieving revision 1.3
diff -c -r1.3 funcapi.h
*** src/include/funcapi.h    18 Jul 2002 04:40:30 -0000    1.3
--- src/include/funcapi.h    21 Jul 2002 05:28:36 -0000
***************
*** 139,144 ****
--- 139,146 ----
   * HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values) -
   *        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.
+  * void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem) - Get
+  *      an attribute "in" function and typelem value given the typeid.
   *
   * Macro declarations:
   * TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple) - get a Datum
Index: src/include/utils/guc.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v
retrieving revision 1.19
diff -c -r1.19 guc.h
*** src/include/utils/guc.h    20 Jul 2002 15:12:56 -0000    1.19
--- src/include/utils/guc.h    20 Jul 2002 23:44:52 -0000
***************
*** 87,93 ****
  extern void ShowGUCConfigOption(const char *name);
  extern void ShowAllGUCConfig(void);
  extern char *GetConfigOptionByName(const char *name, const char **varname);
! extern char *GetConfigOptionByNum(int varnum, const char **varname);
  extern int GetNumConfigOptions(void);

  extern void SetPGVariable(const char *name, List *args, bool is_local);
--- 87,93 ----
  extern void ShowGUCConfigOption(const char *name);
  extern void ShowAllGUCConfig(void);
  extern char *GetConfigOptionByName(const char *name, const char **varname);
! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow);
  extern int GetNumConfigOptions(void);

  extern void SetPGVariable(const char *name, List *args, bool is_local);
Index: doc/src/sgml/xfunc.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
retrieving revision 1.53
diff -c -r1.53 xfunc.sgml
*** doc/src/sgml/xfunc.sgml    18 Jul 2002 04:47:17 -0000    1.53
--- doc/src/sgml/xfunc.sgml    21 Jul 2002 05:29:09 -0000
***************
*** 1557,1562 ****
--- 1557,1570 ----
      </para>

      <para>
+      In order to get an attribute "in" function and typelem value given the
+      typeid, use
+ <programlisting>
+ void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem)
+ </programlisting>
+     </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>