Thread: pg_settings view

pg_settings view

From
Joe Conway
Date:
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)


Re: pg_settings view

From
Tom Lane
Date:
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

Re: pg_settings view

From
Joe Conway
Date:
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)


Re: pg_settings view

From
Tom Lane
Date:
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

Re: pg_settings view

From
Bruce Momjian
Date:
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

Re: pg_settings view

From
Bruce Momjian
Date:
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