Thread: guc GetConfigOptionByNum and tablefunc API - minor changes
Tom Lane wrote:
> One thing that possibly needs discussion is the handling of
> GUC_NO_SHOW_ALL. I moved that test into the SHOW ALL code because
> the intended behavior is for the marked variable to not be in the
> SHOW ALL output at all, rather than be there with a null value as
> your patch originally behaved. Now that was fine for SHOW ALL because
> it can examine the config record directly anyway, but what of external
> callers of GetConfigOptionByNum? There aren't any right now so I'm
> kind of speculating in a vacuum about what they'll want. But it seems
> possible that they will want to be able to discover whether the var is
> marked NO_SHOW_ALL or not. Maybe that should be an additional return
> variable from GetConfigOptionByNum, along the lines of
>
> GetConfigOptionByNum(..., bool *noshow)
> {
> if (noshow)
> *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
> }
>
> Any thoughts?
>
> Oh btw: an Assert() verifying that the arg of GetConfigOptionByNum is
> in range wouldn't be out of place, I think.
Here are two patches. The guc_and_tablefunc patch addresses the two
changes mentioned above, and also adds a new function to the tablefunc
API. The tablefunc API change adds the following function:
* Oid foidGetTypeId(Oid foid) - Get a function's typeid given the
* function Oid. Use this together with TypeGetTupleDesc() to get a
* TupleDesc which is derived from the function's declared return type.
In the next post I'll send the contrib/tablefunc patch, which
illustrates the usage of this new function. Also attached is a doc patch
for this change. The doc patch also adds a function that I failed to
document previously.
If there are no objections, please apply.
Thanks,
Joe
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 01:51:10 -0000
***************
*** 1557,1562 ****
--- 1557,1580 ----
</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>
+ You can use this next function together with TypeGetTupleDesc(), to get
+ a TupleDesc which is derived from the function's declared return type.
+ <programlisting>
+ Oid foidGetTypeId(Oid foid)
+ </programlisting>
+ This allows you to avoid hard-coding the return data type name into your
+ function.
+ </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>
Index: src/backend/utils/fmgr/funcapi.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/utils/fmgr/funcapi.c,v
retrieving revision 1.2
diff -c -r1.2 funcapi.c
*** src/backend/utils/fmgr/funcapi.c 18 Jul 2002 04:40:30 -0000 1.2
--- src/backend/utils/fmgr/funcapi.c 21 Jul 2002 01:04:51 -0000
***************
*** 10,15 ****
--- 10,16 ----
*/
#include "funcapi.h"
+ #include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "utils/syscache.h"
***************
*** 136,139 ****
--- 137,168 ----
*attelem = typtup->typelem;
ReleaseSysCache(typeTuple);
+ }
+
+ /*
+ * get the typeid for a function given the function Oid
+ */
+ Oid
+ foidGetTypeId(Oid foid)
+ {
+ HeapTuple procedureTuple;
+ Form_pg_proc procedureStruct;
+ Oid functypeid;
+
+ /* get the procedure tuple corresponding to the given function Oid */
+ procedureTuple = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(foid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(procedureTuple))
+ elog(ERROR, "foidGetTypeId: Cache lookup failed for procedure %u",
+ foid);
+
+ procedureStruct = (Form_pg_proc) GETSTRUCT(procedureTuple);
+
+ functypeid = procedureStruct->prorettype;
+
+ /* clean up */
+ ReleaseSysCache(procedureTuple);
+
+ return functypeid;
}
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 01:02:26 -0000
***************
*** 139,144 ****
--- 139,149 ----
* 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.
+ * Oid foidGetTypeId(Oid foid) - Get a function's typeid given the function
+ * Oid. Use this together with TypeGetTupleDesc() to get a TupleDesc
+ * which is derived from the function's declared return type.
*
* Macro declarations:
* TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple) - get a Datum
***************
*** 156,161 ****
--- 161,167 ----
/* from funcapi.c */
extern void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem);
+ extern Oid foidGetTypeId(Oid foid);
#define TupleGetDatum(_slot, _tuple) \
PointerGetDatum(ExecStoreTuple(_tuple, _slot, InvalidBuffer, true))
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);
Joe Conway <mail@joeconway.com> writes:
> The tablefunc API change adds the following function:
> * Oid foidGetTypeId(Oid foid) - Get a function's typeid given the
> * function Oid.
Doesn't this duplicate get_func_rettype()? (Which is more clearly
named anyway; a function's type is a second-order concept IMHO...)
regards, tom lane
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...) Yep -- I thought I had seen something that would do this, and I looked for such an animal for a while, but didn't guess close enough to the name to find it :( OK -- three new patches coming. Joe
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>
Joe Conway <mail@joeconway.com> writes:
>> Doesn't this duplicate get_func_rettype()?
> Yep -- I thought I had seen something that would do this, and I looked
> for such an animal for a while, but didn't guess close enough to the
> name to find it :(
You didn't know where to look. Simple system-catalog lookup utilities
of this ilk live in lsyscache.c.
regards, tom lane
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:
> 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''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 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>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.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:
> 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''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 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>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026