Re: show() function - Mailing list pgsql-patches

Tom Lane wrote:
> >Joe Conway <mail@joeconway.com> writes:
>>Short of that, how's this for a plan:
>>1. New backend scalar function and guc.c/guc.h changes (as submitted
>>    except the function name):
>>      current_setting(text setting_name)
>>2. modify "SHOW X" to actually perform the equiv of:
>>      select current_setting('X')
>>3. modify "SHOW ALL" to return query-style output ala EXPLAIN
>>4. submit contrib/showsettings, with a table function
>>    current_settings(), which is a renamed version of the previously
>>    submitted show_all_vars() function
>
> I think the exact SQL function names are still open to debate, but
> otherwise seems like a plan.

The attached patch implements items 1, 2, and 3 above. I also modified
EXPLAIN to use new tuple projecting functions, based on the original
ones in explain.c.

Example:
test=# show debug_print_query;
  debug_print_query
-------------------
  off
(1 row)

test=# show all;
              name              |                setting
-------------------------------+---------------------------------------
  australian_timezones          | off
  authentication_timeout        | 60
  .
   .
    .
  wal_files                     | 0
  wal_sync_method               | fdatasync
(96 rows)

Additionally I created a function called set_config_by_name() which
wraps set_config_option() as a SQL callable function. See below for a
discussion of why I did this.

Notes:
1. Please bump catversion.h. This patch requires initdb.

2. This patch includes the same Table Function API fixes that I
    submitted on July 9:

    http://archives.postgresql.org/pgsql-patches/2002-07/msg00056.php

    Please disregard that one *if* this one is applied. If this one is
    rejected please go ahead with the July 9th patch.

3. I also have a doc patch outstanding:

    http://archives.postgresql.org/pgsql-patches/2002-07/msg00073.php

    Any feedback on this?



 > I was actually alluding to the possibility of a *writable* table, eg
 >
 >     UPDATE pg_settings SET value = 'true' WHERE name =
 > 'debug_print_query';
 >
 > as a query-language equivalent of
 >
 >     SET debug_print_query = true;
 >
 > I believe Oracle already manages some settings this way.
 >
 > A read-only table is easy enough to make from an SRF, see the pg_stats
 > family of views for an example.  I'm not sure how to get the
 > updatability part though ... and am happy to leave it for another day.

Using the show_all_vars() from contrib/showguc (which is *not* part of
the attached patch), and the new set_config_by_name(), I was able to
produce this effect using a VIEW and an UPDATE RULE. See the following:

test=# create view pg_settings as select varname as name, varval as
setting from show_all_vars();
CREATE VIEW
test=# create rule pg_settings_rule as on update to pg_settings do
instead select set_config(old.name, new.setting,'f');
CREATE RULE
test=# UPDATE pg_settings SET setting = 'true' WHERE name =
'debug_print_query';
  set_config
------------
  on
(1 row)

test=# show debug_print_query;
  debug_print_query
-------------------
  on
(1 row)

test=# UPDATE pg_settings SET setting = 'false' WHERE name =
'debug_print_query';
  set_config
------------
  off
(1 row)

test=# show debug_print_query;
  debug_print_query
-------------------
  off
(1 row)

Any interest in rolling show_all_vars(), perhaps renamed
show_all_settings() or something, into the backend and creating a
virtual table in this fashion?

Joe