Re: Table Function API doc patch - Mailing list pgsql-patches
| From | Joe Conway |
|---|---|
| Subject | Re: Table Function API doc patch |
| Date | |
| Msg-id | 3D3A2ED3.3060407@joeconway.com Whole thread Raw |
| In response to | Table Function API doc patch (Joe Conway <mail@joeconway.com>) |
| Responses |
Re: Table Function API doc patch
Re: Table Function API doc patch |
| List | pgsql-patches |
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@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 01:02:57 -0000
***************
*** 0 ****
--- 1,664 ----
+ /*
+ * 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 "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@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;
+
pgsql-patches by date: