diff options
| -rw-r--r-- | contrib/README | 4 | ||||
| -rw-r--r-- | contrib/tablefunc/Makefile | 9 | ||||
| -rw-r--r-- | contrib/tablefunc/README.tablefunc | 272 | ||||
| -rw-r--r-- | contrib/tablefunc/tablefunc-test.sql | 47 | ||||
| -rw-r--r-- | contrib/tablefunc/tablefunc.c | 664 | ||||
| -rw-r--r-- | contrib/tablefunc/tablefunc.h | 39 | ||||
| -rw-r--r-- | contrib/tablefunc/tablefunc.sql.in | 46 |
7 files changed, 1081 insertions, 0 deletions
diff --git a/contrib/README b/contrib/README index 6ae9b2851ca..3bb28836779 100644 --- a/contrib/README +++ b/contrib/README @@ -186,6 +186,10 @@ string - C-like input/output conversion routines for strings by Massimo Dal Zotto <dz@cs.unitn.it> +tablefunc - + Examples of tables returning functions + by Joe Conway <mail@joeconway.com> + tips/apache_logging - Getting Apache to log to PostgreSQL by Terry Mackintosh <terry@terrym.com> diff --git a/contrib/tablefunc/Makefile b/contrib/tablefunc/Makefile new file mode 100644 index 00000000000..0f9c5402dc9 --- /dev/null +++ b/contrib/tablefunc/Makefile @@ -0,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 diff --git a/contrib/tablefunc/README.tablefunc b/contrib/tablefunc/README.tablefunc new file mode 100644 index 00000000000..b3b663aafa2 --- /dev/null +++ b/contrib/tablefunc/README.tablefunc @@ -0,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 + diff --git a/contrib/tablefunc/tablefunc-test.sql b/contrib/tablefunc/tablefunc-test.sql new file mode 100644 index 00000000000..141894b0f41 --- /dev/null +++ b/contrib/tablefunc/tablefunc-test.sql @@ -0,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;'); + + diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c new file mode 100644 index 00000000000..236d833e469 --- /dev/null +++ b/contrib/tablefunc/tablefunc.c @@ -0,0 +1,664 @@ +/* + * tablefunc + * + * Sample to demonstrate C functions which return setof scalar + * and setof composite. + * Joe Conway <mail@joeconway.com> + * + * Copyright 2002 by PostgreSQL Global Development Group + * + * Permission to use, copy, modify, and distribute this software and its + * documentation for any purpose, without fee, and without a written agreement + * is hereby granted, provided that the above copyright notice and this + * paragraph and the following two paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE + * POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES, + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + * + */ +#include <stdlib.h> +#include <math.h> + +#include "postgres.h" + +#include "fmgr.h" +#include "funcapi.h" +#include "executor/spi.h" +#include "utils/builtins.h" +#include "utils/guc.h" + +#include "tablefunc.h" + +static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); +static void get_normal_pair(float8 *x1, float8 *x2); + +typedef struct +{ + float8 mean; /* mean of the distribution */ + float8 stddev; /* stddev of the distribution */ + float8 carry_val; /* hold second generated value */ + bool use_carry; /* use second generated value */ +} normal_rand_fctx; + +typedef struct +{ + SPITupleTable *spi_tuptable; /* sql results from user query */ + char *lastrowid; /* rowid of the last tuple sent */ +} crosstab_fctx; + +#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp))) +#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp))) +#define xpfree(var_) \ + do { \ + if (var_ != NULL) \ + { \ + pfree(var_); \ + var_ = NULL; \ + } \ + } while (0) + +/* + * show_all_settings - equiv to SHOW ALL command but implemented as + * a Table Function. + */ +PG_FUNCTION_INFO_V1(show_all_settings); +Datum +show_all_settings(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + TupleDesc tupdesc; + int call_cntr; + int max_calls; + TupleTableSlot *slot; + AttInMetadata *attinmeta; + + /* stuff done only on the first call of the function */ + if(SRF_IS_FIRSTCALL()) + { + Oid foid = fcinfo->flinfo->fn_oid; + Oid functypeid; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* get the typeid that represents our return type */ + functypeid = foidGetTypeId(foid); + + /* Build a tuple description for a funcrelid tuple */ + tupdesc = TypeGetTupleDesc(functypeid, NIL); + + /* allocate a slot for a tuple with this tupdesc */ + slot = TupleDescGetSlot(tupdesc); + + /* assign slot to function context */ + funcctx->slot = slot; + + /* + * Generate attribute metadata needed later to produce tuples from raw + * C strings + */ + attinmeta = TupleDescGetAttInMetadata(tupdesc); + funcctx->attinmeta = attinmeta; + + /* total number of tuples to be returned */ + funcctx->max_calls = GetNumConfigOptions(); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + + call_cntr = funcctx->call_cntr; + max_calls = funcctx->max_calls; + slot = funcctx->slot; + attinmeta = funcctx->attinmeta; + + if (call_cntr < max_calls) /* do when there is more left to send */ + { + char **values; + char *varname; + char *varval; + bool noshow; + HeapTuple tuple; + Datum result; + + /* + * Get the next visible GUC variable name and value + */ + do + { + varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow); + if (noshow) + { + /* varval is a palloc'd copy, so free it */ + xpfree(varval); + + /* bump the counter and get the next config setting */ + call_cntr = ++funcctx->call_cntr; + + /* make sure we haven't gone too far now */ + if (call_cntr >= max_calls) + SRF_RETURN_DONE(funcctx); + } + } while (noshow); + + /* + * Prepare a values array for storage in our slot. + * This should be an array of C strings which will + * be processed later by the appropriate "in" functions. + */ + values = (char **) palloc(2 * sizeof(char *)); + values[0] = pstrdup(varname); + values[1] = varval; /* varval is already a palloc'd copy */ + + /* build a tuple */ + tuple = BuildTupleFromCStrings(attinmeta, values); + + /* make the tuple into a datum */ + result = TupleGetDatum(slot, tuple); + + /* Clean up */ + xpfree(values[0]); + xpfree(values[1]); + xpfree(values); + + SRF_RETURN_NEXT(funcctx, result); + } + else /* do when there is no more left */ + { + SRF_RETURN_DONE(funcctx); + } +} + +/* + * normal_rand - return requested number of random values + * with a Gaussian (Normal) distribution. + * + * inputs are int numvals, float8 lower_bound, and float8 upper_bound + * returns float8 + */ +PG_FUNCTION_INFO_V1(normal_rand); +Datum +normal_rand(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + int call_cntr; + int max_calls; + normal_rand_fctx *fctx; + float8 mean; + float8 stddev; + float8 carry_val; + bool use_carry; + + /* stuff done only on the first call of the function */ + if(SRF_IS_FIRSTCALL()) + { + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* total number of tuples to be returned */ + funcctx->max_calls = PG_GETARG_UINT32(0); + + /* allocate memory for user context */ + fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx)); + + /* + * Use fctx to keep track of upper and lower bounds + * from call to call. It will also be used to carry over + * the spare value we get from the Box-Muller algorithm + * so that we only actually calculate a new value every + * other call. + */ + fctx->mean = PG_GETARG_FLOAT8(1); + fctx->stddev = PG_GETARG_FLOAT8(2); + fctx->carry_val = 0; + fctx->use_carry = false; + + funcctx->user_fctx = fctx; + + /* + * we might actually get passed a negative number, but for this + * purpose it doesn't matter, just cast it as an unsigned value + */ + srandom(PG_GETARG_UINT32(3)); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + + call_cntr = funcctx->call_cntr; + max_calls = funcctx->max_calls; + fctx = funcctx->user_fctx; + mean = fctx->mean; + stddev = fctx->stddev; + carry_val = fctx->carry_val; + use_carry = fctx->use_carry; + + if (call_cntr < max_calls) /* do when there is more left to send */ + { + float8 result; + + if(use_carry) + { + /* + * reset use_carry and use second value obtained on last pass + */ + fctx->use_carry = false; + result = carry_val; + } + else + { + float8 normval_1; + float8 normval_2; + + /* Get the next two normal values */ + get_normal_pair(&normval_1, &normval_2); + + /* use the first */ + result = mean + (stddev * normval_1); + + /* and save the second */ + fctx->carry_val = mean + (stddev * normval_2); + fctx->use_carry = true; + } + + /* send the result */ + SRF_RETURN_NEXT(funcctx, Float8GetDatum(result)); + } + else /* do when there is no more left */ + { + SRF_RETURN_DONE(funcctx); + } +} + +/* + * get_normal_pair() + * Assigns normally distributed (Gaussian) values to a pair of provided + * parameters, with mean 0, standard deviation 1. + * + * This routine implements Algorithm P (Polar method for normal deviates) + * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages + * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E. + * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611. + * + */ +static void +get_normal_pair(float8 *x1, float8 *x2) +{ + float8 u1, u2, v1, v2, s; + + for(;;) + { + u1 = (float8) random() / (float8) RAND_MAX; + u2 = (float8) random() / (float8) RAND_MAX; + + v1 = (2.0 * u1) - 1.0; + v2 = (2.0 * u2) - 1.0; + + s = pow(v1, 2) + pow(v2, 2); + + if (s >= 1.0) + continue; + + if (s == 0) + { + *x1 = 0; + *x2 = 0; + } + else + { + *x1 = v1 * sqrt((-2.0 * log(s)) / s); + *x2 = v2 * sqrt((-2.0 * log(s)) / s); + } + + return; + } +} + +/* + * crosstab - create a crosstab of rowids and values columns from a + * SQL statement returning one rowid column, one category column, + * and one value column. + * + * e.g. given sql which produces: + * + * rowid cat value + * ------+-------+------- + * row1 cat1 val1 + * row1 cat2 val2 + * row1 cat3 val3 + * row1 cat4 val4 + * row2 cat1 val5 + * row2 cat2 val6 + * row2 cat3 val7 + * row2 cat4 val8 + * + * crosstab returns: + * <===== values columns =====> + * rowid cat1 cat2 cat3 cat4 + * ------+-------+-------+-------+------- + * row1 val1 val2 val3 val4 + * row2 val5 val6 val7 val8 + * + * NOTES: + * 1. SQL result must be ordered by 1,2. + * 2. The number of values columns depends on the tuple description + * of the function's declared return type. + * 2. Missing values (i.e. not enough adjacent rows of same rowid to + * fill the number of result values columns) are filled in with nulls. + * 3. Extra values (i.e. too many adjacent rows of same rowid to fill + * the number of result values columns) are skipped. + * 4. Rows with all nulls in the values columns are skipped. + */ +PG_FUNCTION_INFO_V1(crosstab); +Datum +crosstab(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + TupleDesc ret_tupdesc; + int call_cntr; + int max_calls; + TupleTableSlot *slot; + AttInMetadata *attinmeta; + SPITupleTable *spi_tuptable; + TupleDesc spi_tupdesc; + char *lastrowid; + crosstab_fctx *fctx; + int i; + int num_categories; + + /* stuff done only on the first call of the function */ + if(SRF_IS_FIRSTCALL()) + { + char *sql = GET_STR(PG_GETARG_TEXT_P(0)); + Oid foid = fcinfo->flinfo->fn_oid; + Oid functypeid; + TupleDesc tupdesc; + int ret; + int proc; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* get the typeid that represents our return type */ + functypeid = foidGetTypeId(foid); + + /* Build a tuple description for a funcrelid tuple */ + tupdesc = TypeGetTupleDesc(functypeid, NIL); + + /* allocate a slot for a tuple with this tupdesc */ + slot = TupleDescGetSlot(tupdesc); + + /* assign slot to function context */ + funcctx->slot = slot; + + /* + * Generate attribute metadata needed later to produce tuples from raw + * C strings + */ + attinmeta = TupleDescGetAttInMetadata(tupdesc); + funcctx->attinmeta = attinmeta; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + elog(ERROR, "crosstab: SPI_connect returned %d", ret); + + /* Retrieve the desired rows */ + ret = SPI_exec(sql, 0); + proc = SPI_processed; + + /* Check for qualifying tuples */ + if ((ret == SPI_OK_SELECT) && (proc > 0)) + { + spi_tuptable = SPI_tuptable; + spi_tupdesc = spi_tuptable->tupdesc; + + /* + * The provided SQL query must always return three columns. + * + * 1. rowid the label or identifier for each row in the final + * result + * 2. category the label or identifier for each column in the + * final result + * 3. values the value for each column in the final result + */ + if (spi_tupdesc->natts != 3) + elog(ERROR, "crosstab: provided SQL must return 3 columns;" + " a rowid, a category, and a values column"); + + /* + * Check that return tupdesc is compatible with the one we got + * from ret_relname, at least based on number and type of + * attributes + */ + if (!compatTupleDescs(tupdesc, spi_tupdesc)) + elog(ERROR, "crosstab: return and sql tuple descriptions are" + " incompatible"); + + /* allocate memory for user context */ + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx)); + + /* + * OK, we have data, and it seems to be valid, so save it + * for use across calls + */ + fctx->spi_tuptable = spi_tuptable; + fctx->lastrowid = NULL; + funcctx->user_fctx = fctx; + + /* total number of tuples to be returned */ + funcctx->max_calls = proc; + } + else + { + /* no qualifying tuples */ + funcctx->max_calls = 0; + } + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + + /* + * initialize per-call variables + */ + call_cntr = funcctx->call_cntr; + max_calls = funcctx->max_calls; + + /* return slot for our tuple */ + slot = funcctx->slot; + + /* user context info */ + fctx = (crosstab_fctx *) funcctx->user_fctx; + lastrowid = fctx->lastrowid; + spi_tuptable = fctx->spi_tuptable; + + /* the sql tuple */ + spi_tupdesc = spi_tuptable->tupdesc; + + /* attribute return type and return tuple description */ + attinmeta = funcctx->attinmeta; + ret_tupdesc = attinmeta->tupdesc; + + /* the return tuple always must have 1 rowid + num_categories columns */ + num_categories = ret_tupdesc->natts - 1; + + if (call_cntr < max_calls) /* do when there is more left to send */ + { + HeapTuple tuple; + Datum result; + char **values; + bool allnulls = true; + + while (true) + { + /* allocate space */ + values = (char **) palloc((1 + num_categories) * sizeof(char *)); + + /* and make sure it's clear */ + memset(values, '\0', (1 + num_categories) * sizeof(char *)); + + /* + * now loop through the sql results and assign each value + * in sequence to the next category + */ + for (i = 0; i < num_categories; i++) + { + HeapTuple spi_tuple; + char *rowid; + + /* see if we've gone too far already */ + if (call_cntr >= max_calls) + break; + + /* get the next sql result tuple */ + spi_tuple = spi_tuptable->vals[call_cntr]; + + /* get the rowid from the current sql result tuple */ + rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1); + + /* + * If this is the first pass through the values for this rowid + * set it, otherwise make sure it hasn't changed on us. Also + * check to see if the rowid is the same as that of the last + * tuple sent -- if so, skip this tuple entirely + */ + if (i == 0) + values[0] = pstrdup(rowid); + + if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0)) + { + if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0)) + break; + else if (allnulls == true) + allnulls = false; + + /* + * Get the next category item value, which is alway attribute + * number three. + * + * Be careful to sssign the value to the array index based + * on which category we are presently processing. + */ + values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3); + + /* + * increment the counter since we consume a row + * for each category, but not for last pass + * because the API will do that for us + */ + if (i < (num_categories - 1)) + call_cntr = ++funcctx->call_cntr; + } + else + { + /* + * We'll fill in NULLs for the missing values, + * but we need to decrement the counter since + * this sql result row doesn't belong to the current + * output tuple. + */ + call_cntr = --funcctx->call_cntr; + break; + } + + if (rowid != NULL) + xpfree(rowid); + } + + xpfree(fctx->lastrowid); + + if (values[0] != NULL) + lastrowid = fctx->lastrowid = pstrdup(values[0]); + + if (!allnulls) + { + /* build the tuple */ + tuple = BuildTupleFromCStrings(attinmeta, values); + + /* make the tuple into a datum */ + result = TupleGetDatum(slot, tuple); + + /* Clean up */ + for (i = 0; i < num_categories + 1; i++) + if (values[i] != NULL) + xpfree(values[i]); + xpfree(values); + + SRF_RETURN_NEXT(funcctx, result); + } + else + { + /* + * Skipping this tuple entirely, but we need to advance + * the counter like the API would if we had returned + * one. + */ + call_cntr = ++funcctx->call_cntr; + + /* we'll start over at the top */ + xpfree(values); + + /* see if we've gone too far already */ + if (call_cntr >= max_calls) + { + /* release SPI related resources */ + SPI_finish(); + SRF_RETURN_DONE(funcctx); + } + } + } + } + else /* do when there is no more left */ + { + /* release SPI related resources */ + SPI_finish(); + SRF_RETURN_DONE(funcctx); + } +} + +/* + * Check if two tupdescs match in type of attributes + */ +static bool +compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc) +{ + int i; + Form_pg_attribute ret_attr; + Oid ret_atttypid; + Form_pg_attribute sql_attr; + Oid sql_atttypid; + + /* check the rowid types match */ + ret_atttypid = ret_tupdesc->attrs[0]->atttypid; + sql_atttypid = sql_tupdesc->attrs[0]->atttypid; + if (ret_atttypid != sql_atttypid) + elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match" + " return rowid datatype"); + + /* + * - attribute [1] of the sql tuple is the category; + * no need to check it + * - attribute [2] of the sql tuple should match + * attributes [1] to [natts] of the return tuple + */ + sql_attr = sql_tupdesc->attrs[2]; + for (i = 1; i < ret_tupdesc->natts; i++) + { + ret_attr = ret_tupdesc->attrs[i]; + + if (ret_attr->atttypid != sql_attr->atttypid) + return false; + } + + /* OK, the two tupdescs are compatible for our purposes */ + return true; +} diff --git a/contrib/tablefunc/tablefunc.h b/contrib/tablefunc/tablefunc.h new file mode 100644 index 00000000000..309894ac66c --- /dev/null +++ b/contrib/tablefunc/tablefunc.h @@ -0,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 */ diff --git a/contrib/tablefunc/tablefunc.sql.in b/contrib/tablefunc/tablefunc.sql.in new file mode 100644 index 00000000000..746e8f9cff1 --- /dev/null +++ b/contrib/tablefunc/tablefunc.sql.in @@ -0,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; + |