Re: pg_settings view

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:
> > Joe Conway <mail(at)joeconway(dot)com> writes:
> >>The attached patch takes advantage of this, moving
> >>show_all_settings() from contrib/tablefunc into the backend (renamed
> >>all_settings().
> > That change of name seems like a step backwards to me; it's not more
> > intuitive, and it does seem more likely to conflict with user functions.
> >
> > Actually, if this is going to be primarily a support function for a
> > view, I wonder if it should be pg_show_all_settings.
>
> I agree. Here's a new patch.
>
> For a similar reason I was thinking that only the system view should be
> documented, not the function. Is that the right thing to do?
>
> Joe
>

> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/README.tablefunc,v
> retrieving revision 1.1
> diff -c -r1.1 README.tablefunc
> *** contrib/tablefunc/README.tablefunc 30 Jul 2002 16:31:11 -0000 1.1
> --- contrib/tablefunc/README.tablefunc 10 Aug 2002 22:07:05 -0000
> ***************
> *** 46,54 ****
>
> 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
>
> --- 46,51 ----
> ***************
> *** 58,102 ****
> 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
>
> --- 55,66 ----
> but you can create additional crosstab functions per the instructions
> in the documentation below.
>
> ! crosstab(text sql, N int)
> ! - returns a set of row_name plus N category value columns
> ! - requires anonymous composite type syntax in the FROM clause. See
> ! the instructions in the documentation below.
>
> + Documentation
> ==================================================================
> Name
>
> ***************
> *** 260,265 ****
> --- 224,322 ----
> 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)
> +
> + ==================================================================
> + Name
> +
> + crosstab(text, int) - returns a set of row_name
> + plus N category value columns
> +
> + Synopsis
> +
> + crosstab(text sql, int N)
> +
> + 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
> +
> + N
> +
> + number of category value columns
> +
> + Outputs
> +
> + Returns setof record, which must defined with a column definition
> + in the FROM clause of the SELECT statement, e.g.:
> +
> + SELECT *
> + FROM crosstab(sql, 2) AS ct(row_name text, category_1 text, category_2 text);
> +
> + the example crosstab 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 is determined at run-time. The
> + column definition provided in the FROM clause must provide for
> + N + 1 columns of the proper data types.
> +
> + 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.
> +
> +
> + 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 crosstab(
> + 'select rowid, attribute, value
> + from ct
> + where rowclass = ''group1''
> + and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;', 3)
> + AS ct(row_name text, category_1 text, category_2 text, category_3 text);
>
> row_name | category_1 | category_2 | category_3
> ----------+------------+------------+------------
> Index: contrib/tablefunc/tablefunc-test.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc-test.sql,v
> retrieving revision 1.1
> diff -c -r1.1 tablefunc-test.sql
> *** contrib/tablefunc/tablefunc-test.sql 30 Jul 2002 16:31:11 -0000 1.1
> --- contrib/tablefunc/tablefunc-test.sql 10 Aug 2002 22:07:50 -0000
> ***************
> *** 44,47 ****
> 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;');
>
> !
> --- 44,49 ----
> 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;');
>
> ! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as c(rowid text, att1 text, att2 text);
> ! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowid text, att1 text, att2 text, att3 text);
> ! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as c(rowid text, att1 text, att2 text, att3 text, att4 text);
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
> retrieving revision 1.1
> diff -c -r1.1 tablefunc.c
> *** contrib/tablefunc/tablefunc.c 30 Jul 2002 16:31:11 -0000 1.1
> --- contrib/tablefunc/tablefunc.c 10 Aug 2002 22:10:26 -0000
> ***************
> *** 35,45 ****
> --- 35,47 ----
> #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);
> + static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories);
>
> typedef struct
> {
> ***************
> *** 67,184 ****
> } 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.
> *
> --- 69,74 ----
> ***************
> *** 368,374 ****
> int max_calls;
> TupleTableSlot *slot;
> AttInMetadata *attinmeta;
> ! SPITupleTable *spi_tuptable;
> TupleDesc spi_tupdesc;
> char *lastrowid;
> crosstab_fctx *fctx;
> --- 258,264 ----
> int max_calls;
> TupleTableSlot *slot;
> AttInMetadata *attinmeta;
> ! SPITupleTable *spi_tuptable = NULL;
> TupleDesc spi_tupdesc;
> char *lastrowid;
> crosstab_fctx *fctx;
> ***************
> *** 378,411 ****
> /* 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)
> --- 268,287 ----
> /* 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;
> ! char functyptype;
> ! TupleDesc tupdesc = NULL;
> ! int ret;
> ! int proc;
> ! MemoryContext oldcontext;
>
> /* create a function context for cross-call persistence */
> funcctx = SRF_FIRSTCALL_INIT();
>
> ! /* SPI switches context on us, so save it first */
> ! oldcontext = CurrentMemoryContext;
>
> /* Connect to SPI manager */
> if ((ret = SPI_connect()) < 0)
> ***************
> *** 424,430 ****
> /*
> * 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
> --- 300,306 ----
> /*
> * The provided SQL query must always return three columns.
> *
> ! * 1. rowname the label or identifier for each row in the final
> * result
> * 2. category the label or identifier for each column in the
> * final result
> ***************
> *** 433,467 ****
> 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 */
> --- 309,386 ----
> if (spi_tupdesc->natts != 3)
> elog(ERROR, "crosstab: provided SQL must return 3 columns;"
> " a rowid, a category, and a values column");
> + }
> + else
> + {
> + /* no qualifying tuples */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
>
> ! /* back to the original memory context */
> ! MemoryContextSwitchTo(oldcontext);
>
> ! /* get the typeid that represents our return type */
> ! functypeid = get_func_rettype(funcid);
>
> ! /* check typtype to see if we have a predetermined return type */
> ! functyptype = get_typtype(functypeid);
> !
> ! if (functyptype == 'c')
> ! {
> ! /* Build a tuple description for a functypeid tuple */
> ! tupdesc = TypeGetTupleDesc(functypeid, NIL);
> }
> ! else if (functyptype == 'p' && functypeid == RECORDOID)
> {
> ! if (fcinfo->nargs != 2)
> ! elog(ERROR, "Wrong number of arguments specified for function");
> ! else
> ! {
> ! int num_catagories = PG_GETARG_INT32(1);
> !
> ! tupdesc = make_crosstab_tupledesc(spi_tupdesc, num_catagories);
> ! }
> }
> + else if (functyptype == 'b')
> + elog(ERROR, "Invalid kind of return type specified for function");
> + else
> + elog(ERROR, "Unknown kind of return type specified for function");
> +
> + /*
> + * 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 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;
> +
> + /* allocate memory for user context */
> + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
> +
> + /*
> + * Save spi data 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;
> }
>
> /* stuff done on every call of the function */
> ***************
> *** 662,664 ****
> --- 581,631 ----
> /* OK, the two tupdescs are compatible for our purposes */
> return true;
> }
> +
> + static TupleDesc
> + make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories)
> + {
> + Form_pg_attribute sql_attr;
> + Oid sql_atttypid;
> + TupleDesc tupdesc;
> + int natts;
> + AttrNumber attnum;
> + char attname[NAMEDATALEN];
> + int i;
> +
> + /*
> + * We need to build a tuple description with one column
> + * for the rowname, and num_catagories columns for the values.
> + * Each must be of the same type as the corresponding
> + * spi result input column.
> + */
> + natts = num_catagories + 1;
> + tupdesc = CreateTemplateTupleDesc(natts, WITHOUTOID);
> +
> + /* first the rowname column */
> + attnum = 1;
> +
> + sql_attr = spi_tupdesc->attrs[0];
> + sql_atttypid = sql_attr->atttypid;
> +
> + strcpy(attname, "rowname");
> +
> + TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
> + -1, 0, false);
> +
> + /* now the catagory values columns */
> + sql_attr = spi_tupdesc->attrs[2];
> + sql_atttypid = sql_attr->atttypid;
> +
> + for (i = 0; i < num_catagories; i++)
> + {
> + attnum++;
> +
> + sprintf(attname, "category_%d", i + 1);
> + TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
> + -1, 0, false);
> + }
> +
> + return tupdesc;
> + }
> +
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v
> retrieving revision 1.1
> diff -c -r1.1 tablefunc.h
> *** contrib/tablefunc/tablefunc.h 30 Jul 2002 16:31:11 -0000 1.1
> --- contrib/tablefunc/tablefunc.h 10 Aug 2002 22:07:28 -0000
> ***************
> *** 32,38 ****
> /*
> * External declarations
> */
> - extern Datum show_all_settings(PG_FUNCTION_ARGS);
> extern Datum normal_rand(PG_FUNCTION_ARGS);
> extern Datum crosstab(PG_FUNCTION_ARGS);
>
> --- 32,37 ----
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v
> retrieving revision 1.1
> diff -c -r1.1 tablefunc.sql.in
> *** contrib/tablefunc/tablefunc.sql.in 30 Jul 2002 16:31:11 -0000 1.1
> --- contrib/tablefunc/tablefunc.sql.in 10 Aug 2002 22:07:40 -0000
> ***************
> *** 1,12 ****
> - 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;
> --- 1,3 ----
> ***************
> *** 44,46 ****
> --- 35,40 ----
> RETURNS setof tablefunc_crosstab_4
> AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
>
> + CREATE OR REPLACE FUNCTION crosstab(text,int)
> + RETURNS setof record
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> \ No newline at end of file
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
> retrieving revision 1.78
> diff -c -r1.78 guc.c
> *** src/backend/utils/misc/guc.c 7 Aug 2002 17:26:24 -0000 1.78
> --- src/backend/utils/misc/guc.c 10 Aug 2002 20:44:34 -0000
> ***************
> *** 29,34 ****
> --- 29,35 ----
> #include "commands/vacuum.h"
> #include "executor/executor.h"
> #include "fmgr.h"
> + #include "funcapi.h"
> #include "libpq/auth.h"
> #include "libpq/pqcomm.h"
> #include "mb/pg_wchar.h"
> ***************
> *** 2401,2406 ****
> --- 2402,2518 ----
>
> /* return it */
> PG_RETURN_TEXT_P(result_text);
> + }
> +
> + /*
> + * show_all_settings - equiv to SHOW ALL command but implemented as
> + * a Table Function.
> + */
> + 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())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* need a tuple descriptor representing two TEXT columns */
> + tupdesc = CreateTemplateTupleDesc(2, WITHOUTOID);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
> + TEXTOID, -1, 0, false);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting",
> + TEXTOID, -1, 0, false);
> +
> + /* 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 */
> + if (varval != NULL)
> + pfree(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 */
> + pfree(values[0]);
> + if (varval != NULL)
> + pfree(values[1]);
> + pfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> }
>
> static char *
> Index: src/bin/initdb/initdb.sh
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v
> retrieving revision 1.165
> diff -c -r1.165 initdb.sh
> *** src/bin/initdb/initdb.sh 8 Aug 2002 19:39:05 -0000 1.165
> --- src/bin/initdb/initdb.sh 11 Aug 2002 00:35:29 -0000
> ***************
> *** 1015,1020 ****
> --- 1015,1035 ----
> pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
> FROM pg_database D;
>
> + CREATE VIEW pg_settings AS \
> + SELECT \
> + A.name, \
> + A.setting \
> + FROM pg_show_all_settings() AS A(name text, setting text);
> +
> + CREATE RULE pg_settings_u AS \
> + ON UPDATE TO pg_settings \
> + WHERE new.name = old.name DO \
> + SELECT set_config(old.name, new.setting, 'f');
> +
> + CREATE RULE pg_settings_n AS \
> + ON UPDATE TO pg_settings \
> + DO INSTEAD NOTHING;
> +
> EOF
> if [ "$?" -ne 0 ]; then
> exit_nicely
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
> retrieving revision 1.253
> diff -c -r1.253 pg_proc.h
> *** src/include/catalog/pg_proc.h 9 Aug 2002 16:45:15 -0000 1.253
> --- src/include/catalog/pg_proc.h 11 Aug 2002 00:35:31 -0000
> ***************
> *** 2885,2890 ****
> --- 2885,2892 ----
> DESCR("SHOW X as a function");
> DATA(insert OID = 2078 ( set_config PGNSP PGUID 12 f f f f v 3 25 "25 25 16" set_config_by_name - _null_ ));
> DESCR("SET X as a function");
> + DATA(insert OID = 2084 ( pg_show_all_settings PGNSP PGUID 12 f f t t s 0 2249 "" show_all_settings - _null_ ));
> + DESCR("SHOW ALL as a function");
>
> DATA(insert OID = 2079 ( pg_table_is_visible PGNSP PGUID 12 f f t f s 1 16 "26" pg_table_is_visible - _null_ ));
> DESCR("is table visible in search path?");
> Index: src/include/utils/builtins.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/utils/builtins.h,v
> retrieving revision 1.190
> diff -c -r1.190 builtins.h
> *** src/include/utils/builtins.h 9 Aug 2002 16:45:16 -0000 1.190
> --- src/include/utils/builtins.h 10 Aug 2002 20:21:33 -0000
> ***************
> *** 662,667 ****
> --- 662,668 ----
> /* guc.c */
> extern Datum show_config_by_name(PG_FUNCTION_ARGS);
> extern Datum set_config_by_name(PG_FUNCTION_ARGS);
> + extern Datum show_all_settings(PG_FUNCTION_ARGS);
>
> /* catalog/pg_conversion.c */
> extern Datum pg_convert3(PG_FUNCTION_ARGS);
> Index: src/test/regress/expected/rules.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v
> retrieving revision 1.55
> diff -c -r1.55 rules.out
> *** src/test/regress/expected/rules.out 8 Aug 2002 19:39:05 -0000 1.55
> --- src/test/regress/expected/rules.out 11 Aug 2002 00:43:59 -0000
> ***************
> *** 1269,1274 ****
> --- 1269,1275 ----
> iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
> pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid) AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
> pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
> + pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a;
> pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
> pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
> pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, n.nspname, c.relname;
> ***************
> *** 1304,1315 ****
> shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
> street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
> toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
> ! (38 rows)
>
> SELECT tablename, rulename, definition FROM pg_rules
> ORDER BY tablename, rulename;
> tablename | rulename | definition
> ---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired '::bpchar, '$0.00'::money, old.salary);
> rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired '::bpchar, new.salary, '$0.00'::money);
> rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored '::bpchar, new.salary, old.salary);
> --- 1305,1318 ----
> shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
> street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
> toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
> ! (39 rows)
>
> SELECT tablename, rulename, definition FROM pg_rules
> ORDER BY tablename, rulename;
> tablename | rulename | definition
> ---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> + pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
> + pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, 'f'::boolean) AS set_config;
> rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired '::bpchar, '$0.00'::money, old.salary);
> rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired '::bpchar, new.salary, '$0.00'::money);
> rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored '::bpchar, new.salary, old.salary);
> ***************
> *** 1337,1341 ****
> shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
> shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
> shoelace_ok | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
> ! (27 rows)
>
> --- 1340,1344 ----
> shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
> shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
> shoelace_ok | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
> ! (29 rows)
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073