Re: show() function

Tom Lane wrote:
> >Joe Conway <mail(at)joeconway(dot)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