pg_settings view

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

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(rowid text, att1 text, att2 text);
! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowid text, att1 text, att2 text, att3 text);
! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as c(rowid text, att1 text, att2 text, att3 text, att4 text);
Index: contrib/tablefunc/tablefunc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.c
*** contrib/tablefunc/tablefunc.c	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.c	10 Aug 2002 22:10:26 -0000
***************
*** 35,45 ****
--- 35,47 ----
#include "executor/spi.h" 
#include "utils/builtins.h"
#include "utils/guc.h"
+ #include "utils/lsyscache.h"

#include "tablefunc.h"

static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
static void get_normal_pair(float8 *x1, float8 *x2);
+ static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories);

typedef struct
{
***************
*** 67,184 ****
} while (0)

/*
-  * show_all_settings - equiv to SHOW ALL command but implemented as
-  * a Table Function.
-  */
- PG_FUNCTION_INFO_V1(show_all_settings);
- Datum
- show_all_settings(PG_FUNCTION_ARGS)
- {
- 	FuncCallContext	   *funcctx;
- 	TupleDesc			tupdesc;
- 	int					call_cntr;
- 	int					max_calls;
- 	TupleTableSlot	   *slot;
- 	AttInMetadata	   *attinmeta;
- 
- 	/* stuff done only on the first call of the function */
-  	if(SRF_IS_FIRSTCALL())
-  	{
- 		Oid 		foid = fcinfo->flinfo->fn_oid;
- 		Oid 		functypeid;
- 
- 		/* create a function context for cross-call persistence */
-  		funcctx = SRF_FIRSTCALL_INIT();
- 
- 		/* get the typeid that represents our return type */
- 		functypeid = foidGetTypeId(foid);
- 
- 		/* Build a tuple description for a funcrelid tuple */
- 		tupdesc = TypeGetTupleDesc(functypeid, NIL);
- 
- 		/* allocate a slot for a tuple with this tupdesc */
- 		slot = TupleDescGetSlot(tupdesc);
- 
- 		/* assign slot to function context */
- 		funcctx->slot = slot;
- 
- 		/*
- 		 * Generate attribute metadata needed later to produce tuples from raw
- 		 * C strings
- 		 */
- 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
- 		funcctx->attinmeta = attinmeta;
- 
- 		/* total number of tuples to be returned */
- 		funcctx->max_calls = GetNumConfigOptions();
-     }
- 
- 	/* stuff done on every call of the function */
-  	funcctx = SRF_PERCALL_SETUP();
- 
- 	call_cntr = funcctx->call_cntr;
- 	max_calls = funcctx->max_calls;
- 	slot = funcctx->slot;
- 	attinmeta = funcctx->attinmeta;
- 
-  	if (call_cntr < max_calls)	/* do when there is more left to send */
-  	{
- 		char	   **values;
- 		char	   *varname;
- 		char	   *varval;
- 		bool		noshow;
- 		HeapTuple	tuple;
- 		Datum		result;
- 
- 		/*
- 		 * Get the next visible GUC variable name and value
- 		 */
- 		do
- 		{
- 			varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
- 			if (noshow)
- 			{
- 				/* varval is a palloc'd copy, so free it */
- 				xpfree(varval);
- 
- 				/* bump the counter and get the next config setting */
- 				call_cntr = ++funcctx->call_cntr;
- 
- 				/* make sure we haven't gone too far now */
- 				if (call_cntr >= max_calls)
- 			 		SRF_RETURN_DONE(funcctx);
- 			}
- 		} while (noshow);
- 
- 		/*
- 		 * Prepare a values array for storage in our slot.
- 		 * This should be an array of C strings which will
- 		 * be processed later by the appropriate "in" functions.
- 		 */
- 		values = (char **) palloc(2 * sizeof(char *));
- 		values[0] = pstrdup(varname);
- 		values[1] = varval;	/* varval is already a palloc'd copy */
- 
- 		/* build a tuple */
- 		tuple = BuildTupleFromCStrings(attinmeta, values);
- 
- 		/* make the tuple into a datum */
- 		result = TupleGetDatum(slot, tuple);
- 
- 		/* Clean up */
- 		xpfree(values[0]);
- 		xpfree(values[1]);
- 		xpfree(values);
- 
-  		SRF_RETURN_NEXT(funcctx, result);
-  	}
-  	else	/* do when there is no more left */
-  	{
-  		SRF_RETURN_DONE(funcctx);
-  	}
- }
- 
- /*
* normal_rand - return requested number of random values
* with a Gaussian (Normal) distribution.
*
--- 69,74 ----
***************
*** 368,374 ****
int					max_calls;
TupleTableSlot	   *slot;
AttInMetadata	   *attinmeta;
! 	SPITupleTable	   *spi_tuptable;
TupleDesc			spi_tupdesc;
char			   *lastrowid;
crosstab_fctx	   *fctx;
--- 258,264 ----
int					max_calls;
TupleTableSlot	   *slot;
AttInMetadata	   *attinmeta;
! 	SPITupleTable	   *spi_tuptable = NULL;
TupleDesc			spi_tupdesc;
char			   *lastrowid;
crosstab_fctx	   *fctx;
***************
*** 378,411 ****
/* stuff done only on the first call of the function */
if(SRF_IS_FIRSTCALL())
{
! 		char	   *sql = GET_STR(PG_GETARG_TEXT_P(0));
! 		Oid 		foid = fcinfo->flinfo->fn_oid;
! 		Oid 		functypeid;
! 		TupleDesc	tupdesc;
! 		int			ret;
! 		int			proc;

/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();

! /* get the typeid that represents our return type */
! functypeid = foidGetTypeId(foid);
!
! /* Build a tuple description for a funcrelid tuple */
! tupdesc = TypeGetTupleDesc(functypeid, NIL);
!
! /* allocate a slot for a tuple with this tupdesc */
! slot = TupleDescGetSlot(tupdesc);
!
! /* assign slot to function context */
! funcctx->slot = slot;
!
! /*
! * Generate attribute metadata needed later to produce tuples from raw
! * C strings
! */
! attinmeta = TupleDescGetAttInMetadata(tupdesc);
! funcctx->attinmeta = attinmeta;

/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
--- 268,287 ----
/* stuff done only on the first call of the function */
if(SRF_IS_FIRSTCALL())
{
! 		char		   *sql = GET_STR(PG_GETARG_TEXT_P(0));
! 		Oid 			funcid = fcinfo->flinfo->fn_oid;
! 		Oid 			functypeid;
! 		char			functyptype;
! 		TupleDesc		tupdesc = NULL;
! 		int				ret;
! 		int				proc;
! 		MemoryContext	oldcontext;

/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();

! /* SPI switches context on us, so save it first */
! oldcontext = CurrentMemoryContext;

/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
***************
*** 424,430 ****
/*
* The provided SQL query must always return three columns.
*
! 			 * 1. rowid		the label or identifier for each row in the final
*				result
* 2. category	the label or identifier for each column in the
*				final result
--- 300,306 ----
/*
* The provided SQL query must always return three columns.
*
! 			 * 1. rowname	the label or identifier for each row in the final
*				result
* 2. category	the label or identifier for each column in the
*				final result
***************
*** 433,467 ****
if (spi_tupdesc->natts != 3)
elog(ERROR, "crosstab: provided SQL must return 3 columns;"
" a rowid, a category, and a values column");

! /*
! * Check that return tupdesc is compatible with the one we got
! * from ret_relname, at least based on number and type of
! * attributes
! */
! if (!compatTupleDescs(tupdesc, spi_tupdesc))
! elog(ERROR, "crosstab: return and sql tuple descriptions are"
! " incompatible");
!
! /* allocate memory for user context */
! fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));

! /*
! * OK, we have data, and it seems to be valid, so save it
! * for use across calls
! */
! fctx->spi_tuptable = spi_tuptable;
! fctx->lastrowid = NULL;
! funcctx->user_fctx = fctx;

! /* total number of tuples to be returned */
! funcctx->max_calls = proc;
}
! else
{
! /* no qualifying tuples */
! funcctx->max_calls = 0;
}
}

/* stuff done on every call of the function */
--- 309,386 ----
if (spi_tupdesc->natts != 3)
elog(ERROR, "crosstab: provided SQL must return 3 columns;"
" a rowid, a category, and a values column");
+ 		}
+ 		else
+ 		{
+ 			/* no qualifying tuples */
+ 			SPI_finish();
+ 	 		SRF_RETURN_DONE(funcctx);
+ 		}

! /* back to the original memory context */
! MemoryContextSwitchTo(oldcontext);

! /* get the typeid that represents our return type */
! functypeid = get_func_rettype(funcid);

! 		/* check typtype to see if we have a predetermined return type */
! 		functyptype = get_typtype(functypeid);
! 		
! 		if (functyptype == 'c')
! 		{
! 			/* Build a tuple description for a functypeid tuple */
! 			tupdesc = TypeGetTupleDesc(functypeid, NIL);
}
! 		else if (functyptype == 'p' && functypeid == RECORDOID)
{
! 			if (fcinfo->nargs != 2)
! 				elog(ERROR, "Wrong number of arguments specified for function");
! 			else
! 			{
! 				int	num_catagories = PG_GETARG_INT32(1);
! 
! 				tupdesc = make_crosstab_tupledesc(spi_tupdesc, num_catagories);
! 			}
}
+ 		else if (functyptype == 'b')
+ 			elog(ERROR, "Invalid kind of return type specified for function");
+ 		else
+ 			elog(ERROR, "Unknown kind of return type specified for function");
+ 
+ 		/*
+ 		 * Check that return tupdesc is compatible with the one we got
+ 		 * from ret_relname, at least based on number and type of
+ 		 * attributes
+ 		 */
+ 		if (!compatTupleDescs(tupdesc, spi_tupdesc))
+ 			elog(ERROR, "crosstab: return and sql tuple descriptions are"
+ 									" incompatible");
+ 
+ 		/* allocate a slot for a tuple with this tupdesc */
+ 		slot = TupleDescGetSlot(tupdesc);
+ 
+ 		/* assign slot to function context */
+ 		funcctx->slot = slot;
+ 
+ 		/*
+ 		 * Generate attribute metadata needed later to produce tuples from raw
+ 		 * C strings
+ 		 */
+ 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
+ 		funcctx->attinmeta = attinmeta;
+ 
+ 		/* allocate memory for user context */
+ 		fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
+ 
+ 		/*
+ 		 * Save spi data for use across calls
+ 		 */
+ 		fctx->spi_tuptable = spi_tuptable;
+ 		fctx->lastrowid = NULL;
+ 		funcctx->user_fctx = fctx;
+ 
+ 		/* total number of tuples to be returned */
+ 		funcctx->max_calls = proc;
}

/* stuff done on every call of the function */
***************
*** 662,664 ****
--- 581,631 ----
/* OK, the two tupdescs are compatible for our purposes */
return true;
}
+ 
+ static TupleDesc
+ make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories)
+ {
+ 	Form_pg_attribute	sql_attr;
+ 	Oid					sql_atttypid;
+ 	TupleDesc			tupdesc;
+ 	int					natts;
+ 	AttrNumber			attnum;
+ 	char				attname[NAMEDATALEN];
+ 	int					i;
+ 
+ 	/*
+ 	 * We need to build a tuple description with one column
+ 	 * for the rowname, and num_catagories columns for the values.
+ 	 * Each must be of the same type as the corresponding
+ 	 * spi result input column.
+ 	 */
+ 	natts = num_catagories + 1;
+ 	tupdesc = CreateTemplateTupleDesc(natts, WITHOUTOID);
+ 
+ 	/* first the rowname column */
+ 	attnum = 1;
+ 
+ 	sql_attr = spi_tupdesc->attrs[0];
+ 	sql_atttypid = sql_attr->atttypid;
+ 
+ 	strcpy(attname, "rowname");
+ 
+ 	TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
+ 					   -1, 0, false);
+ 
+ 	/* now the catagory values columns */
+ 	sql_attr = spi_tupdesc->attrs[2];
+ 	sql_atttypid = sql_attr->atttypid;
+ 
+ 	for (i = 0; i < num_catagories; i++)
+ 	{
+ 		attnum++;
+ 
+ 		sprintf(attname, "category_%d", i + 1);
+ 		TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
+ 						   -1, 0, false);
+ 	}
+ 
+ 	return tupdesc;
+ }
+ 
Index: contrib/tablefunc/tablefunc.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.h
*** contrib/tablefunc/tablefunc.h	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.h	10 Aug 2002 22:07:28 -0000
***************
*** 32,38 ****
/*
* External declarations
*/
- extern Datum show_all_settings(PG_FUNCTION_ARGS);
extern Datum normal_rand(PG_FUNCTION_ARGS);
extern Datum crosstab(PG_FUNCTION_ARGS);

--- 32,37 ----
Index: contrib/tablefunc/tablefunc.sql.in
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.sql.in
*** contrib/tablefunc/tablefunc.sql.in	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.sql.in	10 Aug 2002 22:07:40 -0000
***************
*** 1,12 ****
- CREATE VIEW tablefunc_config_settings AS
-   SELECT
-     ''::TEXT AS name,
-     ''::TEXT AS setting;
- 
- CREATE OR REPLACE FUNCTION show_all_settings()
-   RETURNS setof tablefunc_config_settings
-   AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
- 
CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
RETURNS setof float8
AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
--- 1,3 ----
***************
*** 44,46 ****
--- 35,40 ----
RETURNS setof tablefunc_crosstab_4
AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;

+ CREATE OR REPLACE FUNCTION crosstab(text,int)
+   RETURNS setof record
+   AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
\ No newline at end of file
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.78
diff -c -r1.78 guc.c
*** src/backend/utils/misc/guc.c	7 Aug 2002 17:26:24 -0000	1.78
--- src/backend/utils/misc/guc.c	10 Aug 2002 20:44:34 -0000
***************
*** 29,34 ****
--- 29,35 ----
#include "commands/vacuum.h"
#include "executor/executor.h"
#include "fmgr.h"
+ #include "funcapi.h"
#include "libpq/auth.h"
#include "libpq/pqcomm.h"
#include "mb/pg_wchar.h"
***************
*** 2401,2406 ****
--- 2402,2518 ----

/* return it */
PG_RETURN_TEXT_P(result_text);
+ }
+ 
+ /*
+  * show_all_settings - equiv to SHOW ALL command but implemented as
+  * a Table Function.
+  */
+ Datum
+ show_all_settings(PG_FUNCTION_ARGS)
+ {
+ 	FuncCallContext	   *funcctx;
+ 	TupleDesc			tupdesc;
+ 	int					call_cntr;
+ 	int					max_calls;
+ 	TupleTableSlot	   *slot;
+ 	AttInMetadata	   *attinmeta;
+ 
+ 	/* stuff done only on the first call of the function */
+  	if(SRF_IS_FIRSTCALL())
+  	{
+ 		/* create a function context for cross-call persistence */
+  		funcctx = SRF_FIRSTCALL_INIT();
+ 
+ 		/* need a tuple descriptor representing two TEXT columns */
+ 		tupdesc = CreateTemplateTupleDesc(2, WITHOUTOID);
+ 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ 						   TEXTOID, -1, 0, false);
+ 		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting",
+ 						   TEXTOID, -1, 0, false);
+ 
+ 		/* allocate a slot for a tuple with this tupdesc */
+ 		slot = TupleDescGetSlot(tupdesc);
+ 
+ 		/* assign slot to function context */
+ 		funcctx->slot = slot;
+ 
+ 		/*
+ 		 * Generate attribute metadata needed later to produce tuples from raw
+ 		 * C strings
+ 		 */
+ 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
+ 		funcctx->attinmeta = attinmeta;
+ 
+ 		/* total number of tuples to be returned */
+ 		funcctx->max_calls = GetNumConfigOptions();
+     }
+ 
+ 	/* stuff done on every call of the function */
+  	funcctx = SRF_PERCALL_SETUP();
+ 
+ 	call_cntr = funcctx->call_cntr;
+ 	max_calls = funcctx->max_calls;
+ 	slot = funcctx->slot;
+ 	attinmeta = funcctx->attinmeta;
+ 
+  	if (call_cntr < max_calls)	/* do when there is more left to send */
+  	{
+ 		char	   **values;
+ 		char	   *varname;
+ 		char	   *varval;
+ 		bool		noshow;
+ 		HeapTuple	tuple;
+ 		Datum		result;
+ 
+ 		/*
+ 		 * Get the next visible GUC variable name and value
+ 		 */
+ 		do
+ 		{
+ 			varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
+ 			if (noshow)
+ 			{
+ 				/* varval is a palloc'd copy, so free it */
+ 				if (varval != NULL)
+ 					pfree(varval);
+ 
+ 				/* bump the counter and get the next config setting */
+ 				call_cntr = ++funcctx->call_cntr;
+ 
+ 				/* make sure we haven't gone too far now */
+ 				if (call_cntr >= max_calls)
+ 			 		SRF_RETURN_DONE(funcctx);
+ 			}
+ 		} while (noshow);
+ 
+ 		/*
+ 		 * Prepare a values array for storage in our slot.
+ 		 * This should be an array of C strings which will
+ 		 * be processed later by the appropriate "in" functions.
+ 		 */
+ 		values = (char **) palloc(2 * sizeof(char *));
+ 		values[0] = pstrdup(varname);
+ 		values[1] = varval;	/* varval is already a palloc'd copy */
+ 
+ 		/* build a tuple */
+ 		tuple = BuildTupleFromCStrings(attinmeta, values);
+ 
+ 		/* make the tuple into a datum */
+ 		result = TupleGetDatum(slot, tuple);
+ 
+ 		/* Clean up */
+ 		pfree(values[0]);
+ 		if (varval != NULL)
+ 			pfree(values[1]);
+ 		pfree(values);
+ 
+  		SRF_RETURN_NEXT(funcctx, result);
+  	}
+  	else	/* do when there is no more left */
+  	{
+  		SRF_RETURN_DONE(funcctx);
+  	}
}

static char *
Index: src/bin/initdb/initdb.sh
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v
retrieving revision 1.165
diff -c -r1.165 initdb.sh
*** src/bin/initdb/initdb.sh	8 Aug 2002 19:39:05 -0000	1.165
--- src/bin/initdb/initdb.sh	11 Aug 2002 00:35:29 -0000
***************
*** 1015,1020 ****
--- 1015,1035 ----
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
FROM pg_database D;

+ CREATE VIEW pg_settings AS \
+     SELECT \
+             A.name, \
+             A.setting \
+     FROM pg_show_all_settings() AS A(name text, setting text);
+ 
+ CREATE RULE pg_settings_u AS \
+     ON UPDATE TO pg_settings \
+     WHERE new.name = old.name DO \
+     SELECT set_config(old.name, new.setting, 'f');
+ 
+ CREATE RULE pg_settings_n AS \
+     ON UPDATE TO pg_settings \
+     DO INSTEAD NOTHING;
+ 
EOF
if [ "$?" -ne 0 ]; then
exit_nicely
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.253
diff -c -r1.253 pg_proc.h
*** src/include/catalog/pg_proc.h	9 Aug 2002 16:45:15 -0000	1.253
--- src/include/catalog/pg_proc.h	11 Aug 2002 00:35:31 -0000
***************
*** 2885,2890 ****
--- 2885,2892 ----
DESCR("SHOW X as a function");
DATA(insert OID = 2078 (  set_config		PGNSP PGUID 12 f f f f v 3 25 "25 25 16" set_config_by_name - _null_ ));
DESCR("SET X as a function");
+ DATA(insert OID = 2084 (  pg_show_all_settings	PGNSP PGUID 12 f f t t s 0 2249 "" show_all_settings - _null_ ));
+ DESCR("SHOW ALL as a function");

DATA(insert OID = 2079 (  pg_table_is_visible		PGNSP PGUID 12 f f t f s 1 16 "26"  pg_table_is_visible - _null_ ));
DESCR("is table visible in search path?");
Index: src/include/utils/builtins.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.190
diff -c -r1.190 builtins.h
*** src/include/utils/builtins.h	9 Aug 2002 16:45:16 -0000	1.190
--- src/include/utils/builtins.h	10 Aug 2002 20:21:33 -0000
***************
*** 662,667 ****
--- 662,668 ----
/* guc.c */
extern Datum show_config_by_name(PG_FUNCTION_ARGS);
extern Datum set_config_by_name(PG_FUNCTION_ARGS);
+ extern Datum show_all_settings(PG_FUNCTION_ARGS);

/* catalog/pg_conversion.c */
extern Datum pg_convert3(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v
retrieving revision 1.55
diff -c -r1.55 rules.out
*** src/test/regress/expected/rules.out	8 Aug 2002 19:39:05 -0000	1.55
--- src/test/regress/expected/rules.out	11 Aug 2002 00:43:59 -0000
***************
*** 1269,1274 ****
--- 1269,1275 ----
iexit                    | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid) AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
+  pg_settings              | SELECT a.name, a.setting FROM pg_show_all_settings() a;
pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, n.nspname, c.relname;
***************
*** 1304,1315 ****
shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (38 rows)

SELECT tablename, rulename, definition FROM pg_rules 
ORDER BY tablename, rulename;
tablename   |    rulename     |                                                                                                                                  definition                                                                                                                                   
---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired     '::bpchar, '$0.00'::money, old.salary);
rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired     '::bpchar, new.salary, '$0.00'::money);
rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored   '::bpchar, new.salary, old.salary);
--- 1305,1318 ----
shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (39 rows)

SELECT tablename, rulename, definition FROM pg_rules 
ORDER BY tablename, rulename;
tablename   |    rulename     |                                                                                                                                  definition                                                                                                                                   
---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+  pg_settings   | pg_settings_n   | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
+  pg_settings   | pg_settings_u   | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, 'f'::boolean) AS set_config;
rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired     '::bpchar, '$0.00'::money, old.salary);
rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired     '::bpchar, new.salary, '$0.00'::money);
rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored   '::bpchar, new.salary, old.salary);
***************
*** 1337,1341 ****
shoelace      | shoelace_upd    | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
shoelace_data | log_shoelace    | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
shoelace_ok   | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
! (27 rows)

--- 1340,1344 ----
shoelace      | shoelace_upd    | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
shoelace_data | log_shoelace    | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
shoelace_ok   | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
! (29 rows)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@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(rowid text, att1 text, att2 text);
! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as c(rowid text, att1 text, att2 text, att3 text);
! select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as c(rowid text, att1 text, att2 text, att3 text, att4 text);
Index: contrib/tablefunc/tablefunc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.c
*** contrib/tablefunc/tablefunc.c	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.c	10 Aug 2002 22:10:26 -0000
***************
*** 35,45 ****
--- 35,47 ----
#include "executor/spi.h" 
#include "utils/builtins.h"
#include "utils/guc.h"
+ #include "utils/lsyscache.h"

#include "tablefunc.h"

static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
static void get_normal_pair(float8 *x1, float8 *x2);
+ static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories);

typedef struct
{
***************
*** 67,184 ****
} while (0)

/*
-  * show_all_settings - equiv to SHOW ALL command but implemented as
-  * a Table Function.
-  */
- PG_FUNCTION_INFO_V1(show_all_settings);
- Datum
- show_all_settings(PG_FUNCTION_ARGS)
- {
- 	FuncCallContext	   *funcctx;
- 	TupleDesc			tupdesc;
- 	int					call_cntr;
- 	int					max_calls;
- 	TupleTableSlot	   *slot;
- 	AttInMetadata	   *attinmeta;
- 
- 	/* stuff done only on the first call of the function */
-  	if(SRF_IS_FIRSTCALL())
-  	{
- 		Oid 		foid = fcinfo->flinfo->fn_oid;
- 		Oid 		functypeid;
- 
- 		/* create a function context for cross-call persistence */
-  		funcctx = SRF_FIRSTCALL_INIT();
- 
- 		/* get the typeid that represents our return type */
- 		functypeid = foidGetTypeId(foid);
- 
- 		/* Build a tuple description for a funcrelid tuple */
- 		tupdesc = TypeGetTupleDesc(functypeid, NIL);
- 
- 		/* allocate a slot for a tuple with this tupdesc */
- 		slot = TupleDescGetSlot(tupdesc);
- 
- 		/* assign slot to function context */
- 		funcctx->slot = slot;
- 
- 		/*
- 		 * Generate attribute metadata needed later to produce tuples from raw
- 		 * C strings
- 		 */
- 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
- 		funcctx->attinmeta = attinmeta;
- 
- 		/* total number of tuples to be returned */
- 		funcctx->max_calls = GetNumConfigOptions();
-     }
- 
- 	/* stuff done on every call of the function */
-  	funcctx = SRF_PERCALL_SETUP();
- 
- 	call_cntr = funcctx->call_cntr;
- 	max_calls = funcctx->max_calls;
- 	slot = funcctx->slot;
- 	attinmeta = funcctx->attinmeta;
- 
-  	if (call_cntr < max_calls)	/* do when there is more left to send */
-  	{
- 		char	   **values;
- 		char	   *varname;
- 		char	   *varval;
- 		bool		noshow;
- 		HeapTuple	tuple;
- 		Datum		result;
- 
- 		/*
- 		 * Get the next visible GUC variable name and value
- 		 */
- 		do
- 		{
- 			varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
- 			if (noshow)
- 			{
- 				/* varval is a palloc'd copy, so free it */
- 				xpfree(varval);
- 
- 				/* bump the counter and get the next config setting */
- 				call_cntr = ++funcctx->call_cntr;
- 
- 				/* make sure we haven't gone too far now */
- 				if (call_cntr >= max_calls)
- 			 		SRF_RETURN_DONE(funcctx);
- 			}
- 		} while (noshow);
- 
- 		/*
- 		 * Prepare a values array for storage in our slot.
- 		 * This should be an array of C strings which will
- 		 * be processed later by the appropriate "in" functions.
- 		 */
- 		values = (char **) palloc(2 * sizeof(char *));
- 		values[0] = pstrdup(varname);
- 		values[1] = varval;	/* varval is already a palloc'd copy */
- 
- 		/* build a tuple */
- 		tuple = BuildTupleFromCStrings(attinmeta, values);
- 
- 		/* make the tuple into a datum */
- 		result = TupleGetDatum(slot, tuple);
- 
- 		/* Clean up */
- 		xpfree(values[0]);
- 		xpfree(values[1]);
- 		xpfree(values);
- 
-  		SRF_RETURN_NEXT(funcctx, result);
-  	}
-  	else	/* do when there is no more left */
-  	{
-  		SRF_RETURN_DONE(funcctx);
-  	}
- }
- 
- /*
* normal_rand - return requested number of random values
* with a Gaussian (Normal) distribution.
*
--- 69,74 ----
***************
*** 368,374 ****
int					max_calls;
TupleTableSlot	   *slot;
AttInMetadata	   *attinmeta;
! 	SPITupleTable	   *spi_tuptable;
TupleDesc			spi_tupdesc;
char			   *lastrowid;
crosstab_fctx	   *fctx;
--- 258,264 ----
int					max_calls;
TupleTableSlot	   *slot;
AttInMetadata	   *attinmeta;
! 	SPITupleTable	   *spi_tuptable = NULL;
TupleDesc			spi_tupdesc;
char			   *lastrowid;
crosstab_fctx	   *fctx;
***************
*** 378,411 ****
/* stuff done only on the first call of the function */
if(SRF_IS_FIRSTCALL())
{
! 		char	   *sql = GET_STR(PG_GETARG_TEXT_P(0));
! 		Oid 		foid = fcinfo->flinfo->fn_oid;
! 		Oid 		functypeid;
! 		TupleDesc	tupdesc;
! 		int			ret;
! 		int			proc;

/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();

! /* get the typeid that represents our return type */
! functypeid = foidGetTypeId(foid);
!
! /* Build a tuple description for a funcrelid tuple */
! tupdesc = TypeGetTupleDesc(functypeid, NIL);
!
! /* allocate a slot for a tuple with this tupdesc */
! slot = TupleDescGetSlot(tupdesc);
!
! /* assign slot to function context */
! funcctx->slot = slot;
!
! /*
! * Generate attribute metadata needed later to produce tuples from raw
! * C strings
! */
! attinmeta = TupleDescGetAttInMetadata(tupdesc);
! funcctx->attinmeta = attinmeta;

/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
--- 268,287 ----
/* stuff done only on the first call of the function */
if(SRF_IS_FIRSTCALL())
{
! 		char		   *sql = GET_STR(PG_GETARG_TEXT_P(0));
! 		Oid 			funcid = fcinfo->flinfo->fn_oid;
! 		Oid 			functypeid;
! 		char			functyptype;
! 		TupleDesc		tupdesc = NULL;
! 		int				ret;
! 		int				proc;
! 		MemoryContext	oldcontext;

/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();

! /* SPI switches context on us, so save it first */
! oldcontext = CurrentMemoryContext;

/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
***************
*** 424,430 ****
/*
* The provided SQL query must always return three columns.
*
! 			 * 1. rowid		the label or identifier for each row in the final
*				result
* 2. category	the label or identifier for each column in the
*				final result
--- 300,306 ----
/*
* The provided SQL query must always return three columns.
*
! 			 * 1. rowname	the label or identifier for each row in the final
*				result
* 2. category	the label or identifier for each column in the
*				final result
***************
*** 433,467 ****
if (spi_tupdesc->natts != 3)
elog(ERROR, "crosstab: provided SQL must return 3 columns;"
" a rowid, a category, and a values column");

! /*
! * Check that return tupdesc is compatible with the one we got
! * from ret_relname, at least based on number and type of
! * attributes
! */
! if (!compatTupleDescs(tupdesc, spi_tupdesc))
! elog(ERROR, "crosstab: return and sql tuple descriptions are"
! " incompatible");
!
! /* allocate memory for user context */
! fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));

! /*
! * OK, we have data, and it seems to be valid, so save it
! * for use across calls
! */
! fctx->spi_tuptable = spi_tuptable;
! fctx->lastrowid = NULL;
! funcctx->user_fctx = fctx;

! /* total number of tuples to be returned */
! funcctx->max_calls = proc;
}
! else
{
! /* no qualifying tuples */
! funcctx->max_calls = 0;
}
}

/* stuff done on every call of the function */
--- 309,386 ----
if (spi_tupdesc->natts != 3)
elog(ERROR, "crosstab: provided SQL must return 3 columns;"
" a rowid, a category, and a values column");
+ 		}
+ 		else
+ 		{
+ 			/* no qualifying tuples */
+ 			SPI_finish();
+ 	 		SRF_RETURN_DONE(funcctx);
+ 		}

! /* back to the original memory context */
! MemoryContextSwitchTo(oldcontext);

! /* get the typeid that represents our return type */
! functypeid = get_func_rettype(funcid);

! 		/* check typtype to see if we have a predetermined return type */
! 		functyptype = get_typtype(functypeid);
! 		
! 		if (functyptype == 'c')
! 		{
! 			/* Build a tuple description for a functypeid tuple */
! 			tupdesc = TypeGetTupleDesc(functypeid, NIL);
}
! 		else if (functyptype == 'p' && functypeid == RECORDOID)
{
! 			if (fcinfo->nargs != 2)
! 				elog(ERROR, "Wrong number of arguments specified for function");
! 			else
! 			{
! 				int	num_catagories = PG_GETARG_INT32(1);
! 
! 				tupdesc = make_crosstab_tupledesc(spi_tupdesc, num_catagories);
! 			}
}
+ 		else if (functyptype == 'b')
+ 			elog(ERROR, "Invalid kind of return type specified for function");
+ 		else
+ 			elog(ERROR, "Unknown kind of return type specified for function");
+ 
+ 		/*
+ 		 * Check that return tupdesc is compatible with the one we got
+ 		 * from ret_relname, at least based on number and type of
+ 		 * attributes
+ 		 */
+ 		if (!compatTupleDescs(tupdesc, spi_tupdesc))
+ 			elog(ERROR, "crosstab: return and sql tuple descriptions are"
+ 									" incompatible");
+ 
+ 		/* allocate a slot for a tuple with this tupdesc */
+ 		slot = TupleDescGetSlot(tupdesc);
+ 
+ 		/* assign slot to function context */
+ 		funcctx->slot = slot;
+ 
+ 		/*
+ 		 * Generate attribute metadata needed later to produce tuples from raw
+ 		 * C strings
+ 		 */
+ 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
+ 		funcctx->attinmeta = attinmeta;
+ 
+ 		/* allocate memory for user context */
+ 		fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
+ 
+ 		/*
+ 		 * Save spi data for use across calls
+ 		 */
+ 		fctx->spi_tuptable = spi_tuptable;
+ 		fctx->lastrowid = NULL;
+ 		funcctx->user_fctx = fctx;
+ 
+ 		/* total number of tuples to be returned */
+ 		funcctx->max_calls = proc;
}

/* stuff done on every call of the function */
***************
*** 662,664 ****
--- 581,631 ----
/* OK, the two tupdescs are compatible for our purposes */
return true;
}
+ 
+ static TupleDesc
+ make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories)
+ {
+ 	Form_pg_attribute	sql_attr;
+ 	Oid					sql_atttypid;
+ 	TupleDesc			tupdesc;
+ 	int					natts;
+ 	AttrNumber			attnum;
+ 	char				attname[NAMEDATALEN];
+ 	int					i;
+ 
+ 	/*
+ 	 * We need to build a tuple description with one column
+ 	 * for the rowname, and num_catagories columns for the values.
+ 	 * Each must be of the same type as the corresponding
+ 	 * spi result input column.
+ 	 */
+ 	natts = num_catagories + 1;
+ 	tupdesc = CreateTemplateTupleDesc(natts, WITHOUTOID);
+ 
+ 	/* first the rowname column */
+ 	attnum = 1;
+ 
+ 	sql_attr = spi_tupdesc->attrs[0];
+ 	sql_atttypid = sql_attr->atttypid;
+ 
+ 	strcpy(attname, "rowname");
+ 
+ 	TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
+ 					   -1, 0, false);
+ 
+ 	/* now the catagory values columns */
+ 	sql_attr = spi_tupdesc->attrs[2];
+ 	sql_atttypid = sql_attr->atttypid;
+ 
+ 	for (i = 0; i < num_catagories; i++)
+ 	{
+ 		attnum++;
+ 
+ 		sprintf(attname, "category_%d", i + 1);
+ 		TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid,
+ 						   -1, 0, false);
+ 	}
+ 
+ 	return tupdesc;
+ }
+ 
Index: contrib/tablefunc/tablefunc.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.h,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.h
*** contrib/tablefunc/tablefunc.h	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.h	10 Aug 2002 22:07:28 -0000
***************
*** 32,38 ****
/*
* External declarations
*/
- extern Datum show_all_settings(PG_FUNCTION_ARGS);
extern Datum normal_rand(PG_FUNCTION_ARGS);
extern Datum crosstab(PG_FUNCTION_ARGS);

--- 32,37 ----
Index: contrib/tablefunc/tablefunc.sql.in
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.sql.in,v
retrieving revision 1.1
diff -c -r1.1 tablefunc.sql.in
*** contrib/tablefunc/tablefunc.sql.in	30 Jul 2002 16:31:11 -0000	1.1
--- contrib/tablefunc/tablefunc.sql.in	10 Aug 2002 22:07:40 -0000
***************
*** 1,12 ****
- CREATE VIEW tablefunc_config_settings AS
-   SELECT
-     ''::TEXT AS name,
-     ''::TEXT AS setting;
- 
- CREATE OR REPLACE FUNCTION show_all_settings()
-   RETURNS setof tablefunc_config_settings
-   AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
- 
CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
RETURNS setof float8
AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
--- 1,3 ----
***************
*** 44,46 ****
--- 35,40 ----
RETURNS setof tablefunc_crosstab_4
AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;

+ CREATE OR REPLACE FUNCTION crosstab(text,int)
+   RETURNS setof record
+   AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
\ No newline at end of file
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.78
diff -c -r1.78 guc.c
*** src/backend/utils/misc/guc.c	7 Aug 2002 17:26:24 -0000	1.78
--- src/backend/utils/misc/guc.c	10 Aug 2002 20:44:34 -0000
***************
*** 29,34 ****
--- 29,35 ----
#include "commands/vacuum.h"
#include "executor/executor.h"
#include "fmgr.h"
+ #include "funcapi.h"
#include "libpq/auth.h"
#include "libpq/pqcomm.h"
#include "mb/pg_wchar.h"
***************
*** 2401,2406 ****
--- 2402,2518 ----

/* return it */
PG_RETURN_TEXT_P(result_text);
+ }
+ 
+ /*
+  * show_all_settings - equiv to SHOW ALL command but implemented as
+  * a Table Function.
+  */
+ Datum
+ show_all_settings(PG_FUNCTION_ARGS)
+ {
+ 	FuncCallContext	   *funcctx;
+ 	TupleDesc			tupdesc;
+ 	int					call_cntr;
+ 	int					max_calls;
+ 	TupleTableSlot	   *slot;
+ 	AttInMetadata	   *attinmeta;
+ 
+ 	/* stuff done only on the first call of the function */
+  	if(SRF_IS_FIRSTCALL())
+  	{
+ 		/* create a function context for cross-call persistence */
+  		funcctx = SRF_FIRSTCALL_INIT();
+ 
+ 		/* need a tuple descriptor representing two TEXT columns */
+ 		tupdesc = CreateTemplateTupleDesc(2, WITHOUTOID);
+ 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ 						   TEXTOID, -1, 0, false);
+ 		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting",
+ 						   TEXTOID, -1, 0, false);
+ 
+ 		/* allocate a slot for a tuple with this tupdesc */
+ 		slot = TupleDescGetSlot(tupdesc);
+ 
+ 		/* assign slot to function context */
+ 		funcctx->slot = slot;
+ 
+ 		/*
+ 		 * Generate attribute metadata needed later to produce tuples from raw
+ 		 * C strings
+ 		 */
+ 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
+ 		funcctx->attinmeta = attinmeta;
+ 
+ 		/* total number of tuples to be returned */
+ 		funcctx->max_calls = GetNumConfigOptions();
+     }
+ 
+ 	/* stuff done on every call of the function */
+  	funcctx = SRF_PERCALL_SETUP();
+ 
+ 	call_cntr = funcctx->call_cntr;
+ 	max_calls = funcctx->max_calls;
+ 	slot = funcctx->slot;
+ 	attinmeta = funcctx->attinmeta;
+ 
+  	if (call_cntr < max_calls)	/* do when there is more left to send */
+  	{
+ 		char	   **values;
+ 		char	   *varname;
+ 		char	   *varval;
+ 		bool		noshow;
+ 		HeapTuple	tuple;
+ 		Datum		result;
+ 
+ 		/*
+ 		 * Get the next visible GUC variable name and value
+ 		 */
+ 		do
+ 		{
+ 			varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
+ 			if (noshow)
+ 			{
+ 				/* varval is a palloc'd copy, so free it */
+ 				if (varval != NULL)
+ 					pfree(varval);
+ 
+ 				/* bump the counter and get the next config setting */
+ 				call_cntr = ++funcctx->call_cntr;
+ 
+ 				/* make sure we haven't gone too far now */
+ 				if (call_cntr >= max_calls)
+ 			 		SRF_RETURN_DONE(funcctx);
+ 			}
+ 		} while (noshow);
+ 
+ 		/*
+ 		 * Prepare a values array for storage in our slot.
+ 		 * This should be an array of C strings which will
+ 		 * be processed later by the appropriate "in" functions.
+ 		 */
+ 		values = (char **) palloc(2 * sizeof(char *));
+ 		values[0] = pstrdup(varname);
+ 		values[1] = varval;	/* varval is already a palloc'd copy */
+ 
+ 		/* build a tuple */
+ 		tuple = BuildTupleFromCStrings(attinmeta, values);
+ 
+ 		/* make the tuple into a datum */
+ 		result = TupleGetDatum(slot, tuple);
+ 
+ 		/* Clean up */
+ 		pfree(values[0]);
+ 		if (varval != NULL)
+ 			pfree(values[1]);
+ 		pfree(values);
+ 
+  		SRF_RETURN_NEXT(funcctx, result);
+  	}
+  	else	/* do when there is no more left */
+  	{
+  		SRF_RETURN_DONE(funcctx);
+  	}
}

static char *
Index: src/bin/initdb/initdb.sh
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v
retrieving revision 1.165
diff -c -r1.165 initdb.sh
*** src/bin/initdb/initdb.sh	8 Aug 2002 19:39:05 -0000	1.165
--- src/bin/initdb/initdb.sh	11 Aug 2002 00:35:29 -0000
***************
*** 1015,1020 ****
--- 1015,1035 ----
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
FROM pg_database D;

+ CREATE VIEW pg_settings AS \
+     SELECT \
+             A.name, \
+             A.setting \
+     FROM pg_show_all_settings() AS A(name text, setting text);
+ 
+ CREATE RULE pg_settings_u AS \
+     ON UPDATE TO pg_settings \
+     WHERE new.name = old.name DO \
+     SELECT set_config(old.name, new.setting, 'f');
+ 
+ CREATE RULE pg_settings_n AS \
+     ON UPDATE TO pg_settings \
+     DO INSTEAD NOTHING;
+ 
EOF
if [ "$?" -ne 0 ]; then
exit_nicely
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.253
diff -c -r1.253 pg_proc.h
*** src/include/catalog/pg_proc.h	9 Aug 2002 16:45:15 -0000	1.253
--- src/include/catalog/pg_proc.h	11 Aug 2002 00:35:31 -0000
***************
*** 2885,2890 ****
--- 2885,2892 ----
DESCR("SHOW X as a function");
DATA(insert OID = 2078 (  set_config		PGNSP PGUID 12 f f f f v 3 25 "25 25 16" set_config_by_name - _null_ ));
DESCR("SET X as a function");
+ DATA(insert OID = 2084 (  pg_show_all_settings	PGNSP PGUID 12 f f t t s 0 2249 "" show_all_settings - _null_ ));
+ DESCR("SHOW ALL as a function");

DATA(insert OID = 2079 (  pg_table_is_visible		PGNSP PGUID 12 f f t f s 1 16 "26"  pg_table_is_visible - _null_ ));
DESCR("is table visible in search path?");
Index: src/include/utils/builtins.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.190
diff -c -r1.190 builtins.h
*** src/include/utils/builtins.h	9 Aug 2002 16:45:16 -0000	1.190
--- src/include/utils/builtins.h	10 Aug 2002 20:21:33 -0000
***************
*** 662,667 ****
--- 662,668 ----
/* guc.c */
extern Datum show_config_by_name(PG_FUNCTION_ARGS);
extern Datum set_config_by_name(PG_FUNCTION_ARGS);
+ extern Datum show_all_settings(PG_FUNCTION_ARGS);

/* catalog/pg_conversion.c */
extern Datum pg_convert3(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v
retrieving revision 1.55
diff -c -r1.55 rules.out
*** src/test/regress/expected/rules.out	8 Aug 2002 19:39:05 -0000	1.55
--- src/test/regress/expected/rules.out	11 Aug 2002 00:43:59 -0000
***************
*** 1269,1274 ****
--- 1269,1275 ----
iexit                    | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid) AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
+  pg_settings              | SELECT a.name, a.setting FROM pg_show_all_settings() a;
pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, n.nspname, c.relname;
***************
*** 1304,1315 ****
shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (38 rows)

SELECT tablename, rulename, definition FROM pg_rules 
ORDER BY tablename, rulename;
tablename   |    rulename     |                                                                                                                                  definition                                                                                                                                   
---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired     '::bpchar, '$0.00'::money, old.salary);
rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired     '::bpchar, new.salary, '$0.00'::money);
rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored   '::bpchar, new.salary, old.salary);
--- 1305,1318 ----
shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (39 rows)

SELECT tablename, rulename, definition FROM pg_rules 
ORDER BY tablename, rulename;
tablename   |    rulename     |                                                                                                                                  definition                                                                                                                                   
---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+  pg_settings   | pg_settings_n   | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
+  pg_settings   | pg_settings_u   | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, 'f'::boolean) AS set_config;
rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired     '::bpchar, '$0.00'::money, old.salary);
rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired     '::bpchar, new.salary, '$0.00'::money);
rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored   '::bpchar, new.salary, old.salary);
***************
*** 1337,1341 ****
shoelace      | shoelace_upd    | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
shoelace_data | log_shoelace    | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
shoelace_ok   | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
! (27 rows)

--- 1340,1344 ----
shoelace      | shoelace_upd    | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
shoelace_data | log_shoelace    | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
shoelace_ok   | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
! (29 rows)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@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