Re: pg_settings view - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: pg_settings view |
Date | |
Msg-id | 200208140525.g7E5P4501432@candle.pha.pa.us Whole thread Raw |
In response to | Re: pg_settings view (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: > > Joe Conway <mail@joeconway.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(rowidtext, att1 text, att2 text); > ! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowidtext, 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(rowidtext, 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, rampr 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_namespacen 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_queryFROM 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.relnameAS 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_classi 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) ASseq_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 JOINpg_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_dataSET 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 shoelaceSET 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_dataSET 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 shoelaceSET 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@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
pgsql-patches by date: