pg_settings view - Mailing list pgsql-patches
| From | Joe Conway |
|---|---|
| Subject | pg_settings view |
| Date | |
| Msg-id | 3D559A02.6030102@joeconway.com Whole thread Raw |
| Responses |
Re: pg_settings view
|
| List | pgsql-patches |
As discussed on several occasions previously, the new anonymous
composite type capability makes it possible to create a system view
based on a table function in a way that is hopefully palatable to
everyone. The attached patch takes advantage of this, moving
show_all_settings() from contrib/tablefunc into the backend (renamed
all_settings(). It is defined as a builtin returning type RECORD. During
initdb a system view is created to expose the same information presently
available through SHOW ALL. For example:
test=# select * from pg_settings where name like '%debug%';
name | setting
-----------------------+---------
debug_assertions | on
debug_pretty_print | off
debug_print_parse | off
debug_print_plan | off
debug_print_query | off
debug_print_rewritten | off
wal_debug | 0
(7 rows)
Additionally during initdb two rules are created which make it possible
to change settings by updating the system view -- a "virtual table" as
Tom put it. Here's an example:
test=# update pg_settings set setting = 'on' where name =
'show_query_stats';
set_config
------------
on
(1 row)
test=# update pg_settings set setting = 'off' where name =
'show_query_stats';
set_config
------------
off
(1 row)
test=# update pg_settings set setting = 'off' where name = 'does not exist';
set_config
------------
(0 rows)
test=# update pg_settings set name = 'why would I do this?' where name =
'show_query_stats';
set_config
------------
(0 rows)
I have adjusted the regression test for rules also. All regression tests
passed.
I have not revised the docs yet. I wanted to see if this would be
accepted before spending time on the documentation, because it has gone
back and forth more than once already ;-)
If there are no objections, please apply.
Thanks,
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 10 Aug 2002 21:31:56 -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 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 10 Aug 2002 20:20: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 ( 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 10 Aug 2002 21:38:18 -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)
ASdefinition 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 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.relname
ASindexrelname, 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) 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
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)
DOSELECT 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
SETsl_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
SETsl_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
SETsl_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
SETsl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
! (29 rows)
pgsql-patches by date: