Thread: pg_settings view
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)
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.
regards, tom lane
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)
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 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.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)
Joe Conway <mail@joeconway.com> writes:
> 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?
Probably so. Given the notational inconvenience of having to specify
the RECORD output type, I can't see much reason why people would want
to call the function directly, as opposed to selecting from the view.
regards, tom lane
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
Patch applied. Thanks.
---------------------------------------------------------------------------
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