Re: guc GetConfigOptionByNum and tablefunc API - minor changes - Mailing list pgsql-patches
| From | Bruce Momjian |
|---|---|
| Subject | Re: guc GetConfigOptionByNum and tablefunc API - minor changes |
| Date | |
| Msg-id | 200207232217.g6NMHSH07812@candle.pha.pa.us Whole thread Raw |
| In response to | Re: guc GetConfigOptionByNum and tablefunc API - minor changes (Joe Conway <mail@joeconway.com>) |
| List | pgsql-patches |
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
pgsql-patches by date: