Thread: show() function
Attached is a patch which (I believe) addresses Peter's concerns with
respect to the previous showguc patch. In this patch, contrib/showguc is
gone, the term GUC is not exposed to user space, and a builtin
show('gucvarname') function returning a single scalar value has been
added, e.g.:
test=# select show('wal_sync_method');
show
-----------
fdatasync
(1 row)
I will work up new table function API examples per Peter's suggestion,
and finish the C API documentation (hopefully before the weekend is
over) in a separate patch.
If there are no further objections, please apply.
Thanks,
Joe
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.70
diff -c -r1.70 guc.c
*** src/backend/utils/misc/guc.c 16 Jun 2002 00:09:12 -0000 1.70
--- src/backend/utils/misc/guc.c 22 Jun 2002 02:33:37 -0000
***************
*** 819,825 ****
static int guc_var_compare(const void *a, const void *b);
! static void _ShowOption(struct config_generic *record);
/*
--- 819,825 ----
static int guc_var_compare(const void *a, const void *b);
! static char *_ShowOption(struct config_generic *record);
/*
***************
*** 2199,2210 ****
ShowGUCConfigOption(const char *name)
{
struct config_generic *record;
record = find_option(name);
if (record == NULL)
elog(ERROR, "Option '%s' is not recognized", name);
! _ShowOption(record);
}
/*
--- 2199,2216 ----
ShowGUCConfigOption(const char *name)
{
struct config_generic *record;
+ char *val;
record = find_option(name);
if (record == NULL)
elog(ERROR, "Option '%s' is not recognized", name);
! val = _ShowOption(record);
! if(val != NULL)
! {
! elog(INFO, "%s is %s", record->name, val);
! pfree(val);
! }
}
/*
***************
*** 2214,2234 ****
ShowAllGUCConfig(void)
{
int i;
for (i = 0; i < num_guc_variables; i++)
{
struct config_generic *conf = guc_variables[i];
if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
! _ShowOption(conf);
}
}
! static void
_ShowOption(struct config_generic *record)
{
char buffer[256];
const char *val;
switch (record->vartype)
{
--- 2220,2314 ----
ShowAllGUCConfig(void)
{
int i;
+ char *val;
for (i = 0; i < num_guc_variables; i++)
{
struct config_generic *conf = guc_variables[i];
if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
! {
! val = _ShowOption(conf);
! if(val != NULL)
! {
! elog(INFO, "%s is %s", conf->name, val);
! pfree(val);
! }
! }
}
}
! /*
! * Return GUC variable value by name
! */
! char *
! GetConfigOptionByName(const char *name)
! {
! struct config_generic *record;
!
! record = find_option(name);
! if (record == NULL)
! elog(ERROR, "Option '%s' is not recognized", name);
!
! return _ShowOption(record);
! }
!
! /*
! * Return GUC variable value and set varname for a specific
! * variable by number.
! */
! char *
! GetConfigOptionByNum(int varnum, char **varname)
! {
! struct config_generic *conf = guc_variables[varnum];
!
! *varname = pstrdup(conf->name);
!
! if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
! return _ShowOption(conf);
! else
! return NULL;
! }
!
! /*
! * Return the total number of GUC variables
! */
! int
! GetNumConfigOptions(void)
! {
! return num_guc_variables;
! }
!
! /*
! * show_config_by_name - equiv to SHOW X command but implemented as
! * a function.
! */
! Datum
! show_config_by_name(PG_FUNCTION_ARGS)
! {
! char *varname;
! char *varval;
! text *result_text;
!
! /* Get the GUC variable name */
! varname = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
!
! /* Get the value */
! varval = GetConfigOptionByName(varname);
!
! /* Convert to text */
! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(varval)));
!
! /* return it */
! PG_RETURN_TEXT_P(result_text);
! }
!
! static char *
_ShowOption(struct config_generic *record)
{
char buffer[256];
const char *val;
+ char *retval;
switch (record->vartype)
{
***************
*** 2292,2298 ****
break;
}
! elog(INFO, "%s is %s", record->name, val);
}
--- 2372,2380 ----
break;
}
! retval = pstrdup(val);
!
! return retval;
}
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.136
diff -c -r1.136 catversion.h
*** src/include/catalog/catversion.h 20 Jun 2002 20:29:43 -0000 1.136
--- src/include/catalog/catversion.h 22 Jun 2002 02:35:42 -0000
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200206151
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200206211
#endif
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.243
diff -c -r1.243 pg_proc.h
*** src/include/catalog/pg_proc.h 20 Jun 2002 20:29:44 -0000 1.243
--- src/include/catalog/pg_proc.h 22 Jun 2002 02:35:28 -0000
***************
*** 2881,2886 ****
--- 2881,2889 ----
DATA(insert OID = 2074 ( substring PGNSP PGUID 14 f f f t f i 3 25 "25 25 25" 100 0 0 100 "select
substring($1,like_escape($2, $3))" - _null_ ));
DESCR("substitutes regular expression with escape argument");
+ DATA(insert OID = 2099 ( show PGNSP PGUID 12 f f f t f s 1 25 "25" 100 0 0 100 show_config_by_name -
_null_));
+ DESCR("SHOW X as a function");
+
/* Aggregates (moved here from pg_aggregate for 7.3) */
DATA(insert OID = 2100 ( avg PGNSP PGUID 12 t f f f f i 1 1700 "20" 100 0 0 100 aggregate_dummy -
_null_));
Index: src/include/utils/builtins.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.186
diff -c -r1.186 builtins.h
*** src/include/utils/builtins.h 20 Jun 2002 20:29:52 -0000 1.186
--- src/include/utils/builtins.h 22 Jun 2002 02:33:23 -0000
***************
*** 633,636 ****
--- 633,639 ----
extern Datum quote_ident(PG_FUNCTION_ARGS);
extern Datum quote_literal(PG_FUNCTION_ARGS);
+ /* guc.c */
+ extern Datum show_config_by_name(PG_FUNCTION_ARGS);
+
#endif /* BUILTINS_H */
Index: src/include/utils/guc.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v
retrieving revision 1.17
diff -c -r1.17 guc.h
*** src/include/utils/guc.h 17 May 2002 01:19:19 -0000 1.17
--- src/include/utils/guc.h 22 Jun 2002 02:23:14 -0000
***************
*** 86,91 ****
--- 86,94 ----
bool isLocal, bool DoIt);
extern void ShowGUCConfigOption(const char *name);
extern void ShowAllGUCConfig(void);
+ extern char *GetConfigOptionByName(const char *name);
+ extern char *GetConfigOptionByNum(int varnum, char **varname);
+ extern int GetNumConfigOptions(void);
extern void SetPGVariable(const char *name, List *args, bool is_local);
extern void GetPGVariable(const char *name);
Joe Conway <mail@joeconway.com> writes:
> ... a builtin
> show('gucvarname') function returning a single scalar value has been
> added, e.g.:
That name seems way too generic -- ie, likely to conflict with
existing user-defined functions. I'd prefer something like
show_variable; though perhaps Peter will have a better idea.
regards, tom lane
Tom Lane wrote: > That name seems way too generic -- ie, likely to conflict with > existing user-defined functions. I'd prefer something like > show_variable; though perhaps Peter will have a better idea. > I thought it was a good parallel to the current command, but I see your point. I'll wait for a while to see if Peter has any suggestions, and then resubmit. Joe
Joe Conway writes: > I thought it was a good parallel to the current command, but I see your > point. I'll wait for a while to see if Peter has any suggestions, and > then resubmit. The only reason this function doesn't exist yet is that I've been avoiding this very question. My personal favorite these days was get_session_characteristic(), which is reasonably SQL-related and doesn't overload any confusing terms such as parameter, variable, or option. However, considering the new transaction-only settings, this name would be incorrect. Again, stealing from SQL, an alternative could be current_characteristic(), which you couldn't really argue against except on the ground that it's pretty weird. -- Peter Eisentraut peter_e@gmx.net
Joe Conway writes:
> Attached is a patch which (I believe) addresses Peter's concerns with
> respect to the previous showguc patch. In this patch, contrib/showguc is
> gone, the term GUC is not exposed to user space, and a builtin
> show('gucvarname') function returning a single scalar value has been
> added, e.g.:
I think this patch still includes a number of leftovers from previous
attempts, such as a get option by number function.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
> Joe Conway writes:
>>Attached is a patch which (I believe) addresses Peter's concerns with
>>respect to the previous showguc patch. In this patch, contrib/showguc is
>>gone, the term GUC is not exposed to user space, and a builtin
>>show('gucvarname') function returning a single scalar value has been
>>added, e.g.:
>
> I think this patch still includes a number of leftovers from previous
> attempts, such as a get option by number function.
I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables), once it was renamed
to remove the reference to GUC.
Without it, it is impossible for a user function to return a list of
config variables without prior knowledge of the config variable names. I
would still like to have this ability. The only workaround without these
functions is to parse "SHOW ALL" output which is an awfully ugly
alternative.
Joe
Peter Eisentraut wrote:
> Joe Conway writes:
>>Attached is a patch which (I believe) addresses Peter's concerns with
>>respect to the previous showguc patch. In this patch, contrib/showguc is
>>gone, the term GUC is not exposed to user space, and a builtin
>>show('gucvarname') function returning a single scalar value has been
>>added, e.g.:
>
> I think this patch still includes a number of leftovers from previous
> attempts, such as a get option by number function.
I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables), once it was renamed
to remove the reference to GUC.
Without it, it is impossible for a user function to return a list of
config variables without prior knowledge of the config variable names. I
would still like to have this ability. The only workaround without these
functions is to parse "SHOW ALL" output which is an awfully ugly
alternative.
Joe
Peter Eisentraut wrote: > The only reason this function doesn't exist yet is that I've been avoiding > this very question. > > My personal favorite these days was get_session_characteristic(), which is > reasonably SQL-related and doesn't overload any confusing terms such as > parameter, variable, or option. However, considering the new > transaction-only settings, this name would be incorrect. Again, stealing > from SQL, an alternative could be current_characteristic(), which you > couldn't really argue against except on the ground that it's pretty weird. Some alternatives: current_setting() current_configuration() current_behavior() Do you like any of these better? Joe
Joe Conway <mail@joeconway.com> writes:
> I was hoping it would be acceptable to leave this function in (as well
> as the one to get the number of config variables), once it was renamed
> to remove the reference to GUC.
> Without it, it is impossible for a user function to return a list of
> config variables without prior knowledge of the config variable names. I
> would still like to have this ability. The only workaround without these
> functions is to parse "SHOW ALL" output which is an awfully ugly
> alternative.
I agreed with your prior comments that making SHOW ALL return
query-style output isn't a complete solution --- we should do that,
but also the GUC variables should be exposed as a (read-only?) table
or function returning set to allow query-language manipulations of the
set. Unless someone's up for the pseudo-table implementation, a contrib
function returning set seems reasonable.
Also, I think Peter was objecting to exposing the name "GUC" at the
SQL function name level. I see no reason to avoid the phrase at the
C level; C code is going to be #including "utils/guc.h" anyway, so...
regards, tom lane
Tom Lane wrote:
> I agreed with your prior comments that making SHOW ALL return
> query-style output isn't a complete solution --- we should do that,
> but also the GUC variables should be exposed as a (read-only?) table
> or function returning set to allow query-language manipulations of the
> set. Unless someone's up for the pseudo-table implementation, a contrib
> function returning set seems reasonable.
>
I'm not sure I understand what you mean by a pseudo-table -- would a
table function wrapped in a system view (pg_settings?) be the same thing
as a pseudo-table?
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
Comments?
Joe
Joe Conway <mail@joeconway.com> writes:
>> Unless someone's up for the pseudo-table implementation, a contrib
>> function returning set seems reasonable.
> I'm not sure I understand what you mean by a pseudo-table -- would a
> table function wrapped in a system view (pg_settings?) be the same thing
> as a pseudo-table?
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.
> 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.
regards, tom lane
Joe Conway writes: > I was hoping it would be acceptable to leave this function in (as well > as the one to get the number of config variables), Why? I thought the SRF can have arbitrary termination actions, so we do you need to count them in advance? -- Peter Eisentraut peter_e@gmx.net
Tom Lane writes: > 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 think this can be done with a stored procedure. Magic tables with side-effects seem weird. Is there anything fundamentally difficult with supporting "PROCEDURE foo()" as equivalent with "FUNCTION foo() RETURNS opaque" and "CALL foo()" as equivalent with "SELECT foo()" and throw away the result. Then you could define a function analogous to SHOW and a procedure analogous to SET and can manipulate the values using the full expression language. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Is there anything fundamentally difficult with supporting "PROCEDURE
> foo()" as equivalent with "FUNCTION foo() RETURNS opaque" and "CALL foo()"
> as equivalent with "SELECT foo()" and throw away the result.
I'd like to see us *not* overload "opaque" with yet another meaning;
see past rants on subject. But as long as there was a distinguishable
representation of "returns void" in pg_proc, I'd see no problem with the
above.
plpgsql presently spells "CALL" as "PERFORM"; should we stick with that
precedent?
regards, tom lane
Peter Eisentraut wrote: > Joe Conway writes: > >>I was hoping it would be acceptable to leave this function in (as well >>as the one to get the number of config variables), > > Why? I thought the SRF can have arbitrary termination actions, so we do > you need to count them in advance? > Well, call it convenience, but num_guc_variables is used throughout guc.c, so it seemed consistent and reliable to use as a termination action. Joe
Joe Conway <mail@joeconway.com> writes:
> Peter Eisentraut wrote:
>> Why? I thought the SRF can have arbitrary termination actions, so we do
>> you need to count them in advance?
> Well, call it convenience, but num_guc_variables is used throughout
> guc.c, so it seemed consistent and reliable to use as a termination action.
Just standing on the sidelines here, but the above looks like a failure
to communicate. I think Peter was asking a generic question: why should
the SRF support API include a count field? And Joe read it as something
very specific: whether the show_all_guc_variables function should employ
a count to decide when it's done.
My take is that the proposed SRF API includes a count field that the
function may use *if it wants to* to store a termination condition.
If not, fine: detect termination however you want. But the availability
of the field simplifies one common coding pattern, without creating any
noticeable overhead for functions that want to do it differently.
If that wasn't what either of you meant, I'll skulk away quietly...
regards, tom lane
Tom Lane wrote: > My take is that the proposed SRF API includes a count field that the > function may use *if it wants to* to store a termination condition. > If not, fine: detect termination however you want. But the availability > of the field simplifies one common coding pattern, without creating any > noticeable overhead for functions that want to do it differently. > > If that wasn't what either of you meant, I'll skulk away quietly... > Right on target from my side ;-) Joe
Tom Lane writes: > I'd like to see us *not* overload "opaque" with yet another meaning; > see past rants on subject. But as long as there was a distinguishable > representation of "returns void" in pg_proc, I'd see no problem with the > above. I am aware of this concern. However, 0 is the most natural way to encode "nothing" in PostgreSQL. Moreover, it would be desirable to be able to declare trigger "routines" as procedures rather than opaque-returning functions, so to preserve compatibility we'd have to make them equivalent. To un-overload type OID 0, the unknown and C string types should be changed to other numbers. > plpgsql presently spells "CALL" as "PERFORM"; should we stick with that > precedent? I think not, because SQL99 says it's CALL (part 2, 15.1). -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I'd like to see us *not* overload "opaque" with yet another meaning;
>> see past rants on subject. But as long as there was a distinguishable
>> representation of "returns void" in pg_proc, I'd see no problem with the
>> above.
> I am aware of this concern. However, 0 is the most natural way to encode
> "nothing" in PostgreSQL. Moreover, it would be desirable to be able to
> declare trigger "routines" as procedures rather than opaque-returning
> functions, so to preserve compatibility we'd have to make them equivalent.
Say what? Trigger routines do not return void ...
> To un-overload type OID 0, the unknown and C string types should be
> changed to other numbers.
Type OID 0 should only be used for "no type at all", as in the unused
slots of an oidvector, or the unused input-type column of a unary
operator. "Returns void" is a distinct concept, as is "returns tuple"
(or however you want to define the result of a trigger), as certainly
is C string. Unknown already has an OID.
I have speculated about inventing a notion of "pseudo types" (perhaps
marked by 'p' in typtype) that would be allowed as function input and/or
result types but not as column datatypes. Then we could create distinct
pseudotypes with distinct OIDs for each of these shades of meaning.
regards, tom lane
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
Attachment
> test=# show all; > name | setting > -------------------------------+--------------------------------------- > australian_timezones | off > authentication_timeout | 60 Does this mean I could capture the output? I.e. Does anything special need to happen to have JDBC and friends return a vector with the info?
Rod Taylor wrote:
>>test=# show all;
>> name | setting
>>-------------------------------+---------------------------------------
>> australian_timezones | off
>> authentication_timeout | 60
>
>
>
> Does this mean I could capture the output? I.e. Does anything special
> need to happen to have JDBC and friends return a vector with the info?
>
I'm not sure how the various interfaces will handle this. I have an SRF
in the form of a contrib function that I'm sure will work with any
interface, but I have yet to get it accepted into cvs for a variety of
reasons. That's the contib/showguc show_all_vars() function I referred
in the post you are replying to. E.g.:
test=# select * from show_all_vars();
varname | varval
-------------------------------+---------------------------------------
australian_timezones | off
authentication_timeout | 60
checkpoint_segments | 3
.
.
.
wal_debug | 0
wal_files | 0
wal_sync_method | fdatasync
(96 rows)
I *think* this function, probably renamed, will end up in
contrib/tablefunc or something similar.
Joe
> Rod Taylor wrote: > >>test=# show all; > >> name | setting > >>-------------------------------+--------------------------------------- > >> australian_timezones | off > >> authentication_timeout | 60 > > > > > > > > Does this mean I could capture the output? I.e. Does anything special > > need to happen to have JDBC and friends return a vector with the info? This will be very useful for phpPgAdmin, as it means we can show server config like MySQL can. Out of interest: of what type are the name and setting columns? Chris
The below type of functionality build in could make life of applications like phppgadmin and pgAdmin a touch easier. That said, ODBC and libpq may be easily taught that it can treat SHOW results like SELECT results. > test=# select * from show_all_vars(); > varname | varval > -------------------------------+--------------------------------------- > australian_timezones | off > authentication_timeout | 60 > checkpoint_segments | 3
Christopher Kings-Lynne wrote: > Out of interest: of what type are the name and setting columns? They are both TEXT. Joe
Joe Conway wrote: > 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. The July 9th Table Function API patch mentioned above is now in CVS, so here is an updated version of the guc patch which should apply cleanly against CVS tip. If there are no objections, please apply. Thanks, Joe Index: src/backend/commands/explain.c =================================================================== RCS file: /opt/src/cvs/pgsql/src/backend/commands/explain.c,v retrieving revision 1.80 diff -c -r1.80 explain.c *** src/backend/commands/explain.c 20 Jun 2002 20:29:27 -0000 1.80 --- src/backend/commands/explain.c 18 Jul 2002 17:54:10 -0000 *************** *** 15,20 **** --- 15,21 ---- #include "access/heapam.h" #include "catalog/pg_type.h" #include "commands/explain.h" + #include "executor/executor.h" #include "executor/instrument.h" #include "lib/stringinfo.h" #include "nodes/print.h" *************** *** 38,52 **** List *rtable; /* range table */ } ExplainState; - typedef struct TextOutputState - { - TupleDesc tupdesc; - DestReceiver *destfunc; - } TextOutputState; - static StringInfo Explain_PlanToString(Plan *plan, ExplainState *es); static void ExplainOneQuery(Query *query, ExplainStmt *stmt, ! TextOutputState *tstate); static void explain_outNode(StringInfo str, Plan *plan, Plan *outer_plan, int indent, ExplainState *es); static void show_scan_qual(List *qual, bool is_or_qual, const char *qlabel, --- 39,47 ---- List *rtable; /* range table */ } ExplainState; static StringInfo Explain_PlanToString(Plan *plan, ExplainState *es); static void ExplainOneQuery(Query *query, ExplainStmt *stmt, ! TupOutputState *tstate); static void explain_outNode(StringInfo str, Plan *plan, Plan *outer_plan, int indent, ExplainState *es); static void show_scan_qual(List *qual, bool is_or_qual, const char *qlabel, *************** *** 59,69 **** static void show_sort_keys(List *tlist, int nkeys, const char *qlabel, StringInfo str, int indent, ExplainState *es); static Node *make_ors_ands_explicit(List *orclauses); - static TextOutputState *begin_text_output(CommandDest dest, char *title); - static void do_text_output(TextOutputState *tstate, char *aline); - static void do_text_output_multiline(TextOutputState *tstate, char *text); - static void end_text_output(TextOutputState *tstate); - /* * ExplainQuery - --- 54,59 ---- *************** *** 73,88 **** ExplainQuery(ExplainStmt *stmt, CommandDest dest) { Query *query = stmt->query; ! TextOutputState *tstate; List *rewritten; List *l; ! tstate = begin_text_output(dest, "QUERY PLAN"); if (query->commandType == CMD_UTILITY) { /* rewriter will not cope with utility statements */ ! do_text_output(tstate, "Utility statements have no plan structure"); } else { --- 63,85 ---- ExplainQuery(ExplainStmt *stmt, CommandDest dest) { Query *query = stmt->query; ! TupOutputState *tstate; ! TupleDesc tupdesc; List *rewritten; List *l; ! /* need a tuple descriptor representing a single TEXT column */ ! tupdesc = CreateTemplateTupleDesc(1); ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "QUERY PLAN", ! TEXTOID, -1, 0, false); ! ! /* prepare for projection of tuples */ ! tstate = begin_tup_output_tupdesc(dest, tupdesc); if (query->commandType == CMD_UTILITY) { /* rewriter will not cope with utility statements */ ! PROJECT_LINE_OF_TEXT("Utility statements have no plan structure"); } else { *************** *** 92,98 **** if (rewritten == NIL) { /* In the case of an INSTEAD NOTHING, tell at least that */ ! do_text_output(tstate, "Query rewrites to nothing"); } else { --- 89,95 ---- if (rewritten == NIL) { /* In the case of an INSTEAD NOTHING, tell at least that */ ! PROJECT_LINE_OF_TEXT("Query rewrites to nothing"); } else { *************** *** 102,113 **** ExplainOneQuery(lfirst(l), stmt, tstate); /* put a blank line between plans */ if (lnext(l) != NIL) ! do_text_output(tstate, ""); } } } ! end_text_output(tstate); } /* --- 99,110 ---- ExplainOneQuery(lfirst(l), stmt, tstate); /* put a blank line between plans */ if (lnext(l) != NIL) ! PROJECT_LINE_OF_TEXT(""); } } } ! end_tup_output(tstate); } /* *************** *** 115,121 **** * print out the execution plan for one query */ static void ! ExplainOneQuery(Query *query, ExplainStmt *stmt, TextOutputState *tstate) { Plan *plan; ExplainState *es; --- 112,118 ---- * print out the execution plan for one query */ static void ! ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate) { Plan *plan; ExplainState *es; *************** *** 125,133 **** if (query->commandType == CMD_UTILITY) { if (query->utilityStmt && IsA(query->utilityStmt, NotifyStmt)) ! do_text_output(tstate, "NOTIFY"); else ! do_text_output(tstate, "UTILITY"); return; } --- 122,130 ---- if (query->commandType == CMD_UTILITY) { if (query->utilityStmt && IsA(query->utilityStmt, NotifyStmt)) ! PROJECT_LINE_OF_TEXT("NOTIFY"); else ! PROJECT_LINE_OF_TEXT("UTILITY"); return; } *************** *** 192,198 **** do_text_output_multiline(tstate, f); pfree(f); if (es->printCost) ! do_text_output(tstate, ""); /* separator line */ } } --- 189,195 ---- do_text_output_multiline(tstate, f); pfree(f); if (es->printCost) ! PROJECT_LINE_OF_TEXT(""); /* separator line */ } } *************** *** 836,914 **** return (Node *) make_orclause(args); } - } - - - /* - * Functions for sending text to the frontend (or other specified destination) - * as though it is a SELECT result. - * - * We tell the frontend that the table structure is a single TEXT column. - */ - - static TextOutputState * - begin_text_output(CommandDest dest, char *title) - { - TextOutputState *tstate; - TupleDesc tupdesc; - - tstate = (TextOutputState *) palloc(sizeof(TextOutputState)); - - /* need a tuple descriptor representing a single TEXT column */ - tupdesc = CreateTemplateTupleDesc(1); - TupleDescInitEntry(tupdesc, (AttrNumber) 1, title, - TEXTOID, -1, 0, false); - - tstate->tupdesc = tupdesc; - tstate->destfunc = DestToFunction(dest); - - (*tstate->destfunc->setup) (tstate->destfunc, (int) CMD_SELECT, - NULL, tupdesc); - - return tstate; - } - - /* write a single line of text */ - static void - do_text_output(TextOutputState *tstate, char *aline) - { - HeapTuple tuple; - Datum values[1]; - char nulls[1]; - - /* form a tuple and send it to the receiver */ - values[0] = DirectFunctionCall1(textin, CStringGetDatum(aline)); - nulls[0] = ' '; - tuple = heap_formtuple(tstate->tupdesc, values, nulls); - (*tstate->destfunc->receiveTuple) (tuple, - tstate->tupdesc, - tstate->destfunc); - pfree(DatumGetPointer(values[0])); - heap_freetuple(tuple); - } - - /* write a chunk of text, breaking at newline characters */ - /* NB: scribbles on its input! */ - static void - do_text_output_multiline(TextOutputState *tstate, char *text) - { - while (*text) - { - char *eol; - - eol = strchr(text, '\n'); - if (eol) - *eol++ = '\0'; - else - eol = text + strlen(text); - do_text_output(tstate, text); - text = eol; - } - } - - static void - end_text_output(TextOutputState *tstate) - { - (*tstate->destfunc->cleanup) (tstate->destfunc); - pfree(tstate); } --- 833,836 ---- Index: src/backend/executor/execTuples.c =================================================================== RCS file: /opt/src/cvs/pgsql/src/backend/executor/execTuples.c,v retrieving revision 1.54 diff -c -r1.54 execTuples.c *** src/backend/executor/execTuples.c 18 Jul 2002 04:40:30 -0000 1.54 --- src/backend/executor/execTuples.c 18 Jul 2002 17:54:10 -0000 *************** *** 790,792 **** --- 790,862 ---- return tuple; } + /* + * Functions for sending tuples to the frontend (or other specified destination) + * as though it is a SELECT result. These are used by utility commands that + * need to project directly to the destination and don't need or want full + * Table Function capability. Currently used by EXPLAIN and SHOW ALL + */ + TupOutputState * + begin_tup_output_tupdesc(CommandDest dest, TupleDesc tupdesc) + { + TupOutputState *tstate; + + tstate = (TupOutputState *) palloc(sizeof(TupOutputState)); + + tstate->tupdesc = tupdesc; + tstate->destfunc = DestToFunction(dest); + + (*tstate->destfunc->setup) (tstate->destfunc, (int) CMD_SELECT, + NULL, tupdesc); + + return tstate; + } + + /* + * write a single tuple + * + * values is a list of the external C string representations of the values + * to be projected. + */ + void + do_tup_output(TupOutputState *tstate, char **values) + { + /* build a tuple from the input strings using the tupdesc */ + AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tstate->tupdesc); + HeapTuple tuple = BuildTupleFromCStrings(attinmeta, values); + + /* send the tuple to the receiver */ + (*tstate->destfunc->receiveTuple) (tuple, + tstate->tupdesc, + tstate->destfunc); + /* clean up */ + heap_freetuple(tuple); + } + + /* write a chunk of text, breaking at newline characters + * NB: scribbles on its input! + * Should only be used for a single TEXT attribute tupdesc. + */ + void + do_text_output_multiline(TupOutputState *tstate, char *text) + { + while (*text) + { + char *eol; + + eol = strchr(text, '\n'); + if (eol) + *eol++ = '\0'; + else + eol = text + strlen(text); + do_tup_output(tstate, &text); + text = eol; + } + } + + void + end_tup_output(TupOutputState *tstate) + { + (*tstate->destfunc->cleanup) (tstate->destfunc); + pfree(tstate); + } Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.72 diff -c -r1.72 guc.c *** src/backend/utils/misc/guc.c 18 Jul 2002 02:02:30 -0000 1.72 --- src/backend/utils/misc/guc.c 18 Jul 2002 17:54:10 -0000 *************** *** 23,30 **** --- 23,32 ---- #include "access/xlog.h" #include "catalog/namespace.h" + #include "catalog/pg_type.h" #include "commands/async.h" #include "commands/variable.h" + #include "executor/executor.h" #include "fmgr.h" #include "libpq/auth.h" #include "libpq/pqcomm.h" *************** *** 826,832 **** static int guc_var_compare(const void *a, const void *b); ! static void _ShowOption(struct config_generic *record); /* --- 828,834 ---- static int guc_var_compare(const void *a, const void *b); ! static char *_ShowOption(struct config_generic *record); /* *************** *** 2168,2173 **** --- 2170,2226 ---- } /* + * SET command wrapped as a SQL callable function. + */ + Datum + set_config_by_name(PG_FUNCTION_ARGS) + { + char *name; + char *value; + char *new_value; + bool is_local; + text *result_text; + + if (PG_ARGISNULL(0)) + elog(ERROR, "SET variable name is required"); + + /* Get the GUC variable name */ + name = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0)))); + + /* Get the desired value or set to NULL for a reset request */ + if (PG_ARGISNULL(1)) + value = NULL; + else + value = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1)))); + + /* + * Get the desired state of is_local. Default to false + * if provided value is NULL + */ + if (PG_ARGISNULL(2)) + is_local = false; + else + is_local = PG_GETARG_BOOL(2); + + /* Note SET DEFAULT (argstring == NULL) is equivalent to RESET */ + set_config_option(name, + value, + (superuser() ? PGC_SUSET : PGC_USERSET), + PGC_S_SESSION, + is_local, + true); + + /* get the new current value */ + new_value = GetConfigOptionByName(name); + + /* Convert return string to text */ + result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(new_value))); + + /* return it */ + PG_RETURN_TEXT_P(result_text); + } + + /* * SHOW command */ void *************** *** 2203,2215 **** void ShowGUCConfigOption(const char *name) { ! struct config_generic *record; ! record = find_option(name); ! if (record == NULL) ! elog(ERROR, "Option '%s' is not recognized", name); ! _ShowOption(record); } /* --- 2256,2281 ---- void ShowGUCConfigOption(const char *name) { ! TupOutputState *tstate; ! TupleDesc tupdesc; ! CommandDest dest = whereToSendOutput; ! char *value; ! ! /* need a tuple descriptor representing a single TEXT column */ ! tupdesc = CreateTemplateTupleDesc(1); ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, (char *) name, ! TEXTOID, -1, 0, false); ! ! /* prepare for projection of tuples */ ! tstate = begin_tup_output_tupdesc(dest, tupdesc); ! /* Get the value */ ! value = GetConfigOptionByName(name); ! /* Send it */ ! PROJECT_LINE_OF_TEXT(value); ! ! end_tup_output(tstate); } /* *************** *** 2219,2235 **** ShowAllGUCConfig(void) { int i; for (i = 0; i < num_guc_variables; i++) { ! struct config_generic *conf = guc_variables[i]; ! if ((conf->flags & GUC_NO_SHOW_ALL) == 0) ! _ShowOption(conf); } } ! static void _ShowOption(struct config_generic *record) { char buffer[256]; --- 2285,2399 ---- ShowAllGUCConfig(void) { int i; + TupOutputState *tstate; + TupleDesc tupdesc; + CommandDest dest = whereToSendOutput; + char *name; + char *value; + char *values[2]; + + /* need a tuple descriptor representing two TEXT columns */ + tupdesc = CreateTemplateTupleDesc(2); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", + TEXTOID, -1, 0, false); + + /* prepare for projection of tuples */ + tstate = begin_tup_output_tupdesc(dest, tupdesc); for (i = 0; i < num_guc_variables; i++) { ! /* Get the next GUC variable name and value */ ! value = GetConfigOptionByNum(i, &name); ! /* assign to the values array */ ! values[0] = name; ! values[1] = value; ! ! /* send it to dest */ ! do_tup_output(tstate, values); ! ! /* ! * clean up ! */ ! /* we always should have a name */ ! pfree(name); ! ! /* but value can be returned to us as a NULL */ ! if (value != NULL) ! pfree(value); } + + end_tup_output(tstate); } ! /* ! * Return GUC variable value by name ! */ ! char * ! GetConfigOptionByName(const char *name) ! { ! struct config_generic *record; ! ! record = find_option(name); ! if (record == NULL) ! elog(ERROR, "Option '%s' is not recognized", name); ! ! return _ShowOption(record); ! } ! ! /* ! * Return GUC variable value and set varname for a specific ! * variable by number. ! */ ! char * ! GetConfigOptionByNum(int varnum, char **varname) ! { ! struct config_generic *conf = guc_variables[varnum]; ! ! *varname = pstrdup(conf->name); ! ! if ((conf->flags & GUC_NO_SHOW_ALL) == 0) ! return _ShowOption(conf); ! else ! return NULL; ! } ! ! /* ! * Return the total number of GUC variables ! */ ! int ! GetNumConfigOptions(void) ! { ! return num_guc_variables; ! } ! ! /* ! * show_config_by_name - equiv to SHOW X command but implemented as ! * a function. ! */ ! Datum ! show_config_by_name(PG_FUNCTION_ARGS) ! { ! char *varname; ! char *varval; ! text *result_text; ! ! /* Get the GUC variable name */ ! varname = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0)))); ! ! /* Get the value */ ! varval = GetConfigOptionByName(varname); ! ! /* Convert to text */ ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(varval))); ! ! /* return it */ ! PG_RETURN_TEXT_P(result_text); ! } ! ! static char * _ShowOption(struct config_generic *record) { char buffer[256]; *************** *** 2297,2303 **** break; } ! elog(INFO, "%s is %s", record->name, val); } --- 2461,2467 ---- break; } ! return pstrdup(val); } Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /opt/src/cvs/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.243 diff -c -r1.243 pg_proc.h *** src/include/catalog/pg_proc.h 20 Jun 2002 20:29:44 -0000 1.243 --- src/include/catalog/pg_proc.h 18 Jul 2002 17:54:10 -0000 *************** *** 2881,2886 **** --- 2881,2891 ---- DATA(insert OID = 2074 ( substring PGNSP PGUID 14 f f f t f i 3 25 "25 25 25" 100 0 0 100 "select substring($1,like_escape($2, $3))" - _null_ )); DESCR("substitutes regular expression with escape argument"); + DATA(insert OID = 2090 ( current_setting PGNSP PGUID 12 f f f t f s 1 25 "25" 100 0 0 100 show_config_by_name - _null_)); + DESCR("SHOW X as a function"); + DATA(insert OID = 2091 ( set_config PGNSP PGUID 12 f f f f f v 3 25 "25 25 16" 100 0 0 100 set_config_by_name -_null_ )); + DESCR("SET X as a function"); + /* Aggregates (moved here from pg_aggregate for 7.3) */ DATA(insert OID = 2100 ( avg PGNSP PGUID 12 t f f f f i 1 1700 "20" 100 0 0 100 aggregate_dummy - _null_)); Index: src/include/executor/executor.h =================================================================== RCS file: /opt/src/cvs/pgsql/src/include/executor/executor.h,v retrieving revision 1.69 diff -c -r1.69 executor.h *** src/include/executor/executor.h 26 Jun 2002 21:58:56 -0000 1.69 --- src/include/executor/executor.h 18 Jul 2002 17:54:10 -0000 *************** *** 121,126 **** --- 121,145 ---- extern TupleDesc ExecTypeFromTL(List *targetList); extern void SetChangedParamList(Plan *node, List *newchg); + typedef struct TupOutputState + { + TupleDesc tupdesc; + DestReceiver *destfunc; + } TupOutputState; + + extern TupOutputState *begin_tup_output_tupdesc(CommandDest dest, TupleDesc tupdesc); + extern void do_tup_output(TupOutputState *tstate, char **values); + extern void do_text_output_multiline(TupOutputState *tstate, char *text); + extern void end_tup_output(TupOutputState *tstate); + + #define PROJECT_LINE_OF_TEXT(text_to_project) \ + do { \ + char *values[1]; \ + values[0] = text_to_project; \ + do_tup_output(tstate, values); \ + } while (0) + + /* * prototypes from functions in execUtils.c */ Index: src/include/utils/builtins.h =================================================================== RCS file: /opt/src/cvs/pgsql/src/include/utils/builtins.h,v retrieving revision 1.186 diff -c -r1.186 builtins.h *** src/include/utils/builtins.h 20 Jun 2002 20:29:52 -0000 1.186 --- src/include/utils/builtins.h 18 Jul 2002 17:54:10 -0000 *************** *** 633,636 **** --- 633,640 ---- extern Datum quote_ident(PG_FUNCTION_ARGS); extern Datum quote_literal(PG_FUNCTION_ARGS); + /* guc.c */ + extern Datum show_config_by_name(PG_FUNCTION_ARGS); + extern Datum set_config_by_name(PG_FUNCTION_ARGS); + #endif /* BUILTINS_H */ Index: src/include/utils/guc.h =================================================================== RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v retrieving revision 1.17 diff -c -r1.17 guc.h *** src/include/utils/guc.h 17 May 2002 01:19:19 -0000 1.17 --- src/include/utils/guc.h 18 Jul 2002 17:54:10 -0000 *************** *** 86,91 **** --- 86,94 ---- bool isLocal, bool DoIt); extern void ShowGUCConfigOption(const char *name); extern void ShowAllGUCConfig(void); + extern char *GetConfigOptionByName(const char *name); + extern char *GetConfigOptionByNum(int varnum, char **varname); + extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); extern void GetPGVariable(const char *name);
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:
> Joe Conway wrote:
> > 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.
>
> The July 9th Table Function API patch mentioned above is now in CVS, so
> here is an updated version of the guc patch which should apply cleanly
> against CVS tip.
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe
> Index: src/backend/commands/explain.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/commands/explain.c,v
> retrieving revision 1.80
> diff -c -r1.80 explain.c
> *** src/backend/commands/explain.c 20 Jun 2002 20:29:27 -0000 1.80
> --- src/backend/commands/explain.c 18 Jul 2002 17:54:10 -0000
> ***************
> *** 15,20 ****
> --- 15,21 ----
> #include "access/heapam.h"
> #include "catalog/pg_type.h"
> #include "commands/explain.h"
> + #include "executor/executor.h"
> #include "executor/instrument.h"
> #include "lib/stringinfo.h"
> #include "nodes/print.h"
> ***************
> *** 38,52 ****
> List *rtable; /* range table */
> } ExplainState;
>
> - typedef struct TextOutputState
> - {
> - TupleDesc tupdesc;
> - DestReceiver *destfunc;
> - } TextOutputState;
> -
> static StringInfo Explain_PlanToString(Plan *plan, ExplainState *es);
> static void ExplainOneQuery(Query *query, ExplainStmt *stmt,
> ! TextOutputState *tstate);
> static void explain_outNode(StringInfo str, Plan *plan, Plan *outer_plan,
> int indent, ExplainState *es);
> static void show_scan_qual(List *qual, bool is_or_qual, const char *qlabel,
> --- 39,47 ----
> List *rtable; /* range table */
> } ExplainState;
>
> static StringInfo Explain_PlanToString(Plan *plan, ExplainState *es);
> static void ExplainOneQuery(Query *query, ExplainStmt *stmt,
> ! TupOutputState *tstate);
> static void explain_outNode(StringInfo str, Plan *plan, Plan *outer_plan,
> int indent, ExplainState *es);
> static void show_scan_qual(List *qual, bool is_or_qual, const char *qlabel,
> ***************
> *** 59,69 ****
> static void show_sort_keys(List *tlist, int nkeys, const char *qlabel,
> StringInfo str, int indent, ExplainState *es);
> static Node *make_ors_ands_explicit(List *orclauses);
> - static TextOutputState *begin_text_output(CommandDest dest, char *title);
> - static void do_text_output(TextOutputState *tstate, char *aline);
> - static void do_text_output_multiline(TextOutputState *tstate, char *text);
> - static void end_text_output(TextOutputState *tstate);
> -
>
> /*
> * ExplainQuery -
> --- 54,59 ----
> ***************
> *** 73,88 ****
> ExplainQuery(ExplainStmt *stmt, CommandDest dest)
> {
> Query *query = stmt->query;
> ! TextOutputState *tstate;
> List *rewritten;
> List *l;
>
> ! tstate = begin_text_output(dest, "QUERY PLAN");
>
> if (query->commandType == CMD_UTILITY)
> {
> /* rewriter will not cope with utility statements */
> ! do_text_output(tstate, "Utility statements have no plan structure");
> }
> else
> {
> --- 63,85 ----
> ExplainQuery(ExplainStmt *stmt, CommandDest dest)
> {
> Query *query = stmt->query;
> ! TupOutputState *tstate;
> ! TupleDesc tupdesc;
> List *rewritten;
> List *l;
>
> ! /* need a tuple descriptor representing a single TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "QUERY PLAN",
> ! TEXTOID, -1, 0, false);
> !
> ! /* prepare for projection of tuples */
> ! tstate = begin_tup_output_tupdesc(dest, tupdesc);
>
> if (query->commandType == CMD_UTILITY)
> {
> /* rewriter will not cope with utility statements */
> ! PROJECT_LINE_OF_TEXT("Utility statements have no plan structure");
> }
> else
> {
> ***************
> *** 92,98 ****
> if (rewritten == NIL)
> {
> /* In the case of an INSTEAD NOTHING, tell at least that */
> ! do_text_output(tstate, "Query rewrites to nothing");
> }
> else
> {
> --- 89,95 ----
> if (rewritten == NIL)
> {
> /* In the case of an INSTEAD NOTHING, tell at least that */
> ! PROJECT_LINE_OF_TEXT("Query rewrites to nothing");
> }
> else
> {
> ***************
> *** 102,113 ****
> ExplainOneQuery(lfirst(l), stmt, tstate);
> /* put a blank line between plans */
> if (lnext(l) != NIL)
> ! do_text_output(tstate, "");
> }
> }
> }
>
> ! end_text_output(tstate);
> }
>
> /*
> --- 99,110 ----
> ExplainOneQuery(lfirst(l), stmt, tstate);
> /* put a blank line between plans */
> if (lnext(l) != NIL)
> ! PROJECT_LINE_OF_TEXT("");
> }
> }
> }
>
> ! end_tup_output(tstate);
> }
>
> /*
> ***************
> *** 115,121 ****
> * print out the execution plan for one query
> */
> static void
> ! ExplainOneQuery(Query *query, ExplainStmt *stmt, TextOutputState *tstate)
> {
> Plan *plan;
> ExplainState *es;
> --- 112,118 ----
> * print out the execution plan for one query
> */
> static void
> ! ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
> {
> Plan *plan;
> ExplainState *es;
> ***************
> *** 125,133 ****
> if (query->commandType == CMD_UTILITY)
> {
> if (query->utilityStmt && IsA(query->utilityStmt, NotifyStmt))
> ! do_text_output(tstate, "NOTIFY");
> else
> ! do_text_output(tstate, "UTILITY");
> return;
> }
>
> --- 122,130 ----
> if (query->commandType == CMD_UTILITY)
> {
> if (query->utilityStmt && IsA(query->utilityStmt, NotifyStmt))
> ! PROJECT_LINE_OF_TEXT("NOTIFY");
> else
> ! PROJECT_LINE_OF_TEXT("UTILITY");
> return;
> }
>
> ***************
> *** 192,198 ****
> do_text_output_multiline(tstate, f);
> pfree(f);
> if (es->printCost)
> ! do_text_output(tstate, ""); /* separator line */
> }
> }
>
> --- 189,195 ----
> do_text_output_multiline(tstate, f);
> pfree(f);
> if (es->printCost)
> ! PROJECT_LINE_OF_TEXT(""); /* separator line */
> }
> }
>
> ***************
> *** 836,914 ****
>
> return (Node *) make_orclause(args);
> }
> - }
> -
> -
> - /*
> - * Functions for sending text to the frontend (or other specified destination)
> - * as though it is a SELECT result.
> - *
> - * We tell the frontend that the table structure is a single TEXT column.
> - */
> -
> - static TextOutputState *
> - begin_text_output(CommandDest dest, char *title)
> - {
> - TextOutputState *tstate;
> - TupleDesc tupdesc;
> -
> - tstate = (TextOutputState *) palloc(sizeof(TextOutputState));
> -
> - /* need a tuple descriptor representing a single TEXT column */
> - tupdesc = CreateTemplateTupleDesc(1);
> - TupleDescInitEntry(tupdesc, (AttrNumber) 1, title,
> - TEXTOID, -1, 0, false);
> -
> - tstate->tupdesc = tupdesc;
> - tstate->destfunc = DestToFunction(dest);
> -
> - (*tstate->destfunc->setup) (tstate->destfunc, (int) CMD_SELECT,
> - NULL, tupdesc);
> -
> - return tstate;
> - }
> -
> - /* write a single line of text */
> - static void
> - do_text_output(TextOutputState *tstate, char *aline)
> - {
> - HeapTuple tuple;
> - Datum values[1];
> - char nulls[1];
> -
> - /* form a tuple and send it to the receiver */
> - values[0] = DirectFunctionCall1(textin, CStringGetDatum(aline));
> - nulls[0] = ' ';
> - tuple = heap_formtuple(tstate->tupdesc, values, nulls);
> - (*tstate->destfunc->receiveTuple) (tuple,
> - tstate->tupdesc,
> - tstate->destfunc);
> - pfree(DatumGetPointer(values[0]));
> - heap_freetuple(tuple);
> - }
> -
> - /* write a chunk of text, breaking at newline characters */
> - /* NB: scribbles on its input! */
> - static void
> - do_text_output_multiline(TextOutputState *tstate, char *text)
> - {
> - while (*text)
> - {
> - char *eol;
> -
> - eol = strchr(text, '\n');
> - if (eol)
> - *eol++ = '\0';
> - else
> - eol = text + strlen(text);
> - do_text_output(tstate, text);
> - text = eol;
> - }
> - }
> -
> - static void
> - end_text_output(TextOutputState *tstate)
> - {
> - (*tstate->destfunc->cleanup) (tstate->destfunc);
> - pfree(tstate);
> }
> --- 833,836 ----
> Index: src/backend/executor/execTuples.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/executor/execTuples.c,v
> retrieving revision 1.54
> diff -c -r1.54 execTuples.c
> *** src/backend/executor/execTuples.c 18 Jul 2002 04:40:30 -0000 1.54
> --- src/backend/executor/execTuples.c 18 Jul 2002 17:54:10 -0000
> ***************
> *** 790,792 ****
> --- 790,862 ----
> return tuple;
> }
>
> + /*
> + * Functions for sending tuples to the frontend (or other specified destination)
> + * as though it is a SELECT result. These are used by utility commands that
> + * need to project directly to the destination and don't need or want full
> + * Table Function capability. Currently used by EXPLAIN and SHOW ALL
> + */
> + TupOutputState *
> + begin_tup_output_tupdesc(CommandDest dest, TupleDesc tupdesc)
> + {
> + TupOutputState *tstate;
> +
> + tstate = (TupOutputState *) palloc(sizeof(TupOutputState));
> +
> + tstate->tupdesc = tupdesc;
> + tstate->destfunc = DestToFunction(dest);
> +
> + (*tstate->destfunc->setup) (tstate->destfunc, (int) CMD_SELECT,
> + NULL, tupdesc);
> +
> + return tstate;
> + }
> +
> + /*
> + * write a single tuple
> + *
> + * values is a list of the external C string representations of the values
> + * to be projected.
> + */
> + void
> + do_tup_output(TupOutputState *tstate, char **values)
> + {
> + /* build a tuple from the input strings using the tupdesc */
> + AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tstate->tupdesc);
> + HeapTuple tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* send the tuple to the receiver */
> + (*tstate->destfunc->receiveTuple) (tuple,
> + tstate->tupdesc,
> + tstate->destfunc);
> + /* clean up */
> + heap_freetuple(tuple);
> + }
> +
> + /* write a chunk of text, breaking at newline characters
> + * NB: scribbles on its input!
> + * Should only be used for a single TEXT attribute tupdesc.
> + */
> + void
> + do_text_output_multiline(TupOutputState *tstate, char *text)
> + {
> + while (*text)
> + {
> + char *eol;
> +
> + eol = strchr(text, '\n');
> + if (eol)
> + *eol++ = '\0';
> + else
> + eol = text + strlen(text);
> + do_tup_output(tstate, &text);
> + text = eol;
> + }
> + }
> +
> + void
> + end_tup_output(TupOutputState *tstate)
> + {
> + (*tstate->destfunc->cleanup) (tstate->destfunc);
> + pfree(tstate);
> + }
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
> retrieving revision 1.72
> diff -c -r1.72 guc.c
> *** src/backend/utils/misc/guc.c 18 Jul 2002 02:02:30 -0000 1.72
> --- src/backend/utils/misc/guc.c 18 Jul 2002 17:54:10 -0000
> ***************
> *** 23,30 ****
> --- 23,32 ----
>
> #include "access/xlog.h"
> #include "catalog/namespace.h"
> + #include "catalog/pg_type.h"
> #include "commands/async.h"
> #include "commands/variable.h"
> + #include "executor/executor.h"
> #include "fmgr.h"
> #include "libpq/auth.h"
> #include "libpq/pqcomm.h"
> ***************
> *** 826,832 ****
>
>
> static int guc_var_compare(const void *a, const void *b);
> ! static void _ShowOption(struct config_generic *record);
>
>
> /*
> --- 828,834 ----
>
>
> static int guc_var_compare(const void *a, const void *b);
> ! static char *_ShowOption(struct config_generic *record);
>
>
> /*
> ***************
> *** 2168,2173 ****
> --- 2170,2226 ----
> }
>
> /*
> + * SET command wrapped as a SQL callable function.
> + */
> + Datum
> + set_config_by_name(PG_FUNCTION_ARGS)
> + {
> + char *name;
> + char *value;
> + char *new_value;
> + bool is_local;
> + text *result_text;
> +
> + if (PG_ARGISNULL(0))
> + elog(ERROR, "SET variable name is required");
> +
> + /* Get the GUC variable name */
> + name = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
> +
> + /* Get the desired value or set to NULL for a reset request */
> + if (PG_ARGISNULL(1))
> + value = NULL;
> + else
> + value = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1))));
> +
> + /*
> + * Get the desired state of is_local. Default to false
> + * if provided value is NULL
> + */
> + if (PG_ARGISNULL(2))
> + is_local = false;
> + else
> + is_local = PG_GETARG_BOOL(2);
> +
> + /* Note SET DEFAULT (argstring == NULL) is equivalent to RESET */
> + set_config_option(name,
> + value,
> + (superuser() ? PGC_SUSET : PGC_USERSET),
> + PGC_S_SESSION,
> + is_local,
> + true);
> +
> + /* get the new current value */
> + new_value = GetConfigOptionByName(name);
> +
> + /* Convert return string to text */
> + result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(new_value)));
> +
> + /* return it */
> + PG_RETURN_TEXT_P(result_text);
> + }
> +
> + /*
> * SHOW command
> */
> void
> ***************
> *** 2203,2215 ****
> void
> ShowGUCConfigOption(const char *name)
> {
> ! struct config_generic *record;
>
> ! record = find_option(name);
> ! if (record == NULL)
> ! elog(ERROR, "Option '%s' is not recognized", name);
>
> ! _ShowOption(record);
> }
>
> /*
> --- 2256,2281 ----
> void
> ShowGUCConfigOption(const char *name)
> {
> ! TupOutputState *tstate;
> ! TupleDesc tupdesc;
> ! CommandDest dest = whereToSendOutput;
> ! char *value;
> !
> ! /* need a tuple descriptor representing a single TEXT column */
> ! tupdesc = CreateTemplateTupleDesc(1);
> ! TupleDescInitEntry(tupdesc, (AttrNumber) 1, (char *) name,
> ! TEXTOID, -1, 0, false);
> !
> ! /* prepare for projection of tuples */
> ! tstate = begin_tup_output_tupdesc(dest, tupdesc);
>
> ! /* Get the value */
> ! value = GetConfigOptionByName(name);
>
> ! /* Send it */
> ! PROJECT_LINE_OF_TEXT(value);
> !
> ! end_tup_output(tstate);
> }
>
> /*
> ***************
> *** 2219,2235 ****
> ShowAllGUCConfig(void)
> {
> int i;
>
> for (i = 0; i < num_guc_variables; i++)
> {
> ! struct config_generic *conf = guc_variables[i];
>
> ! if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
> ! _ShowOption(conf);
> }
> }
>
> ! static void
> _ShowOption(struct config_generic *record)
> {
> char buffer[256];
> --- 2285,2399 ----
> ShowAllGUCConfig(void)
> {
> int i;
> + TupOutputState *tstate;
> + TupleDesc tupdesc;
> + CommandDest dest = whereToSendOutput;
> + char *name;
> + char *value;
> + char *values[2];
> +
> + /* need a tuple descriptor representing two TEXT columns */
> + tupdesc = CreateTemplateTupleDesc(2);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
> + TEXTOID, -1, 0, false);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting",
> + TEXTOID, -1, 0, false);
> +
> + /* prepare for projection of tuples */
> + tstate = begin_tup_output_tupdesc(dest, tupdesc);
>
> for (i = 0; i < num_guc_variables; i++)
> {
> ! /* Get the next GUC variable name and value */
> ! value = GetConfigOptionByNum(i, &name);
>
> ! /* assign to the values array */
> ! values[0] = name;
> ! values[1] = value;
> !
> ! /* send it to dest */
> ! do_tup_output(tstate, values);
> !
> ! /*
> ! * clean up
> ! */
> ! /* we always should have a name */
> ! pfree(name);
> !
> ! /* but value can be returned to us as a NULL */
> ! if (value != NULL)
> ! pfree(value);
> }
> +
> + end_tup_output(tstate);
> }
>
> ! /*
> ! * Return GUC variable value by name
> ! */
> ! char *
> ! GetConfigOptionByName(const char *name)
> ! {
> ! struct config_generic *record;
> !
> ! record = find_option(name);
> ! if (record == NULL)
> ! elog(ERROR, "Option '%s' is not recognized", name);
> !
> ! return _ShowOption(record);
> ! }
> !
> ! /*
> ! * Return GUC variable value and set varname for a specific
> ! * variable by number.
> ! */
> ! char *
> ! GetConfigOptionByNum(int varnum, char **varname)
> ! {
> ! struct config_generic *conf = guc_variables[varnum];
> !
> ! *varname = pstrdup(conf->name);
> !
> ! if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
> ! return _ShowOption(conf);
> ! else
> ! return NULL;
> ! }
> !
> ! /*
> ! * Return the total number of GUC variables
> ! */
> ! int
> ! GetNumConfigOptions(void)
> ! {
> ! return num_guc_variables;
> ! }
> !
> ! /*
> ! * show_config_by_name - equiv to SHOW X command but implemented as
> ! * a function.
> ! */
> ! Datum
> ! show_config_by_name(PG_FUNCTION_ARGS)
> ! {
> ! char *varname;
> ! char *varval;
> ! text *result_text;
> !
> ! /* Get the GUC variable name */
> ! varname = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
> !
> ! /* Get the value */
> ! varval = GetConfigOptionByName(varname);
> !
> ! /* Convert to text */
> ! result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(varval)));
> !
> ! /* return it */
> ! PG_RETURN_TEXT_P(result_text);
> ! }
> !
> ! static char *
> _ShowOption(struct config_generic *record)
> {
> char buffer[256];
> ***************
> *** 2297,2303 ****
> break;
> }
>
> ! elog(INFO, "%s is %s", record->name, val);
> }
>
>
> --- 2461,2467 ----
> break;
> }
>
> ! return pstrdup(val);
> }
>
>
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/catalog/pg_proc.h,v
> retrieving revision 1.243
> diff -c -r1.243 pg_proc.h
> *** src/include/catalog/pg_proc.h 20 Jun 2002 20:29:44 -0000 1.243
> --- src/include/catalog/pg_proc.h 18 Jul 2002 17:54:10 -0000
> ***************
> *** 2881,2886 ****
> --- 2881,2891 ----
> DATA(insert OID = 2074 ( substring PGNSP PGUID 14 f f f t f i 3 25 "25 25 25" 100 0 0 100 "select
substring($1,like_escape($2, $3))" - _null_ ));
> DESCR("substitutes regular expression with escape argument");
>
> + DATA(insert OID = 2090 ( current_setting PGNSP PGUID 12 f f f t f s 1 25 "25" 100 0 0 100 show_config_by_name -
_null_));
> + DESCR("SHOW X as a function");
> + DATA(insert OID = 2091 ( set_config PGNSP PGUID 12 f f f f f v 3 25 "25 25 16" 100 0 0 100
set_config_by_name- _null_ ));
> + DESCR("SET X as a function");
> +
> /* Aggregates (moved here from pg_aggregate for 7.3) */
>
> DATA(insert OID = 2100 ( avg PGNSP PGUID 12 t f f f f i 1 1700 "20" 100 0 0 100 aggregate_dummy -
_null_));
> Index: src/include/executor/executor.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/executor/executor.h,v
> retrieving revision 1.69
> diff -c -r1.69 executor.h
> *** src/include/executor/executor.h 26 Jun 2002 21:58:56 -0000 1.69
> --- src/include/executor/executor.h 18 Jul 2002 17:54:10 -0000
> ***************
> *** 121,126 ****
> --- 121,145 ----
> extern TupleDesc ExecTypeFromTL(List *targetList);
> extern void SetChangedParamList(Plan *node, List *newchg);
>
> + typedef struct TupOutputState
> + {
> + TupleDesc tupdesc;
> + DestReceiver *destfunc;
> + } TupOutputState;
> +
> + extern TupOutputState *begin_tup_output_tupdesc(CommandDest dest, TupleDesc tupdesc);
> + extern void do_tup_output(TupOutputState *tstate, char **values);
> + extern void do_text_output_multiline(TupOutputState *tstate, char *text);
> + extern void end_tup_output(TupOutputState *tstate);
> +
> + #define PROJECT_LINE_OF_TEXT(text_to_project) \
> + do { \
> + char *values[1]; \
> + values[0] = text_to_project; \
> + do_tup_output(tstate, values); \
> + } while (0)
> +
> +
> /*
> * prototypes from functions in execUtils.c
> */
> Index: src/include/utils/builtins.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/utils/builtins.h,v
> retrieving revision 1.186
> diff -c -r1.186 builtins.h
> *** src/include/utils/builtins.h 20 Jun 2002 20:29:52 -0000 1.186
> --- src/include/utils/builtins.h 18 Jul 2002 17:54:10 -0000
> ***************
> *** 633,636 ****
> --- 633,640 ----
> extern Datum quote_ident(PG_FUNCTION_ARGS);
> extern Datum quote_literal(PG_FUNCTION_ARGS);
>
> + /* guc.c */
> + extern Datum show_config_by_name(PG_FUNCTION_ARGS);
> + extern Datum set_config_by_name(PG_FUNCTION_ARGS);
> +
> #endif /* BUILTINS_H */
> Index: src/include/utils/guc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v
> retrieving revision 1.17
> diff -c -r1.17 guc.h
> *** src/include/utils/guc.h 17 May 2002 01:19:19 -0000 1.17
> --- src/include/utils/guc.h 18 Jul 2002 17:54:10 -0000
> ***************
> *** 86,91 ****
> --- 86,94 ----
> bool isLocal, bool DoIt);
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> + extern char *GetConfigOptionByName(const char *name);
> + extern char *GetConfigOptionByNum(int varnum, char **varname);
> + extern int GetNumConfigOptions(void);
>
> extern void SetPGVariable(const char *name, List *args, bool is_local);
> extern void GetPGVariable(const char *name);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > Joe Conway wrote: > > 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. > > The July 9th Table Function API patch mentioned above is now in CVS, so > here is an updated version of the guc patch which should apply cleanly > against CVS tip. > > If there are no objections, please apply. > > Thanks, > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Joe Conway <mail@joeconway.com> writes:
> The July 9th Table Function API patch mentioned above is now in CVS, so
> here is an updated version of the guc patch which should apply cleanly
> against CVS tip.
This patch is lacking documentation additions for the newly-defined
functions; not to mention doc updates for the new behavior of SHOW.
Please supply...
regards, tom lane
Tom Lane wrote: > This patch is lacking documentation additions for the newly-defined > functions; not to mention doc updates for the new behavior of SHOW. > Please supply... Will do (should be done today). Joe
Tom Lane wrote:
> This patch is lacking documentation additions for the newly-defined
> functions; not to mention doc updates for the new behavior of SHOW.
> Please supply...
Here is a doc patch for the SHOW X changes and new config-settings
functions. If there are no objections, please apply.
Thanks,
Joe
Index: doc/src/sgml/ref/show.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql/doc/src/sgml/ref/show.sgml,v
retrieving revision 1.17
diff -c -r1.17 show.sgml
*** doc/src/sgml/ref/show.sgml 17 May 2002 01:19:16 -0000 1.17
--- doc/src/sgml/ref/show.sgml 20 Jul 2002 16:54:21 -0000
***************
*** 83,89 ****
<screen>
SHOW DateStyle;
! INFO: DateStyle is ISO with US (NonEuropean) conventions
</screen>
</para>
--- 83,92 ----
<screen>
SHOW DateStyle;
! DateStyle
! ---------------------------------------
! ISO with US (NonEuropean) conventions
! (1 row)
</screen>
</para>
***************
*** 91,99 ****
Show the current genetic optimizer (<literal>geqo</literal>) setting:
<screen>
SHOW GEQO;
! INFO: geqo is on
</screen>
</para>
</refsect1>
<refsect1 id="R1-SQL-SHOW-3">
--- 94,125 ----
Show the current genetic optimizer (<literal>geqo</literal>) setting:
<screen>
SHOW GEQO;
! geqo
! ------
! on
! (1 row)
</screen>
</para>
+
+ <para>
+ Show all settings:
+ <screen>
+ SHOW ALL;
+ name | setting
+ -------------------------------+---------------------------------------
+ australian_timezones | off
+ authentication_timeout | 60
+ checkpoint_segments | 3
+ .
+ .
+ .
+ wal_debug | 0
+ wal_files | 0
+ wal_sync_method | fdatasync
+ (94 rows)
+ </screen>
+ </para>
+
</refsect1>
<refsect1 id="R1-SQL-SHOW-3">
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.104
diff -c -r1.104 func.sgml
*** doc/src/sgml/func.sgml 24 Jun 2002 22:17:01 -0000 1.104
--- doc/src/sgml/func.sgml 20 Jul 2002 20:34:04 -0000
***************
*** 4437,4442 ****
--- 4437,4555 ----
</para>
<table>
+ <title>Configuration Settings Information Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <function>current_setting</function>(<parameter>setting_name</parameter>)
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>value of current setting</entry>
+ </row>
+ <row>
+ <entry>
+ <function>set_config(<parameter>setting_name</parameter>,
+ <parameter>new_value</parameter>,
+ <parameter>is_local</parameter>)</function>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>new value of current setting</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <indexterm zone="functions-misc">
+ <primary>setting</primary>
+ <secondary>current</secondary>
+ </indexterm>
+
+ <indexterm zone="functions-misc">
+ <primary>setting</primary>
+ <secondary>set</secondary>
+ </indexterm>
+
+ <para>
+ The <function>current_setting</function> is used to obtain the current
+ value of the <parameter>setting_name</parameter> setting, as a query
+ result. For example:
+ <programlisting>
+ select current_setting('DateStyle');
+ current_setting
+ ---------------------------------------
+ ISO with US (NonEuropean) conventions
+ (1 row)
+ </programlisting>
+ </para>
+
+ <para>
+ <function>set_config</function> allows the <parameter>setting_name
+ </parameter> setting to be changed to <parameter>new_value</parameter>.
+ If <parameter>is_local</parameter> is set to <literal>true</literal>,
+ the new value will only apply to the current transaction. If you want
+ the new value to apply for the current session, use <literal>false</literal>
+ instead. For example:
+ <programlisting>
+ SHOW show_query_stats;
+ show_query_stats
+ ------------------
+ on
+ (1 row)
+
+ select set_config('show_query_stats','off','f');
+ set_config
+ ------------
+ off
+ (1 row)
+
+ SHOW show_query_stats;
+ show_query_stats
+ ------------------
+ off
+ (1 row)
+
+ select set_config('show_query_stats','on','t');
+ set_config
+ ------------
+ on
+ (1 row)
+
+ SHOW show_query_stats;
+ show_query_stats
+ ------------------
+ off
+ (1 row)
+
+ BEGIN;
+ BEGIN
+ select set_config('show_query_stats','on','t');
+ set_config
+ ------------
+ on
+ (1 row)
+
+ SHOW show_query_stats;
+ show_query_stats
+ ------------------
+ on
+ (1 row)
+
+ COMMIT;
+ COMMIT
+ SHOW show_query_stats;
+ show_query_stats
+ ------------------
+ off
+ (1 row)
+ </programlisting>
+ </para>
+
+ <table>
<title>Access Privilege Inquiry Functions</title>
<tgroup cols="3">
<thead>
Joe Conway <mail@joeconway.com> writes:
> Here is a doc patch for the SHOW X changes and new config-settings
> functions. If there are no objections, please apply.
It might be good if the func.sgml part said in so many words that these
functions are equivalent to SHOW and SET respectively; and perhaps the
ref pages for SHOW and SET should cross-reference the functions.
Otherwise seems fine.
regards, tom lane
Tom Lane wrote: > It might be good if the func.sgml part said in so many words that these > functions are equivalent to SHOW and SET respectively; and perhaps the > ref pages for SHOW and SET should cross-reference the functions. > Otherwise seems fine. > OK - done. Here's a new patch. Thanks, Joe
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:
> > It might be good if the func.sgml part said in so many words that these
> > functions are equivalent to SHOW and SET respectively; and perhaps the
> > ref pages for SHOW and SET should cross-reference the functions.
> > Otherwise seems fine.
> >
>
> OK - done. Here's a new patch.
>
> Thanks,
>
> Joe
[ text/html is unsupported, treating like TEXT/PLAIN ]
> Index: doc/src/sgml/func.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/func.sgml,v
> retrieving revision 1.104
> diff -c -r1.104 func.sgml
> *** doc/src/sgml/func.sgml 24 Jun 2002 22:17:01 -0000 1.104
> --- doc/src/sgml/func.sgml 20 Jul 2002 22:16:16 -0000
> ***************
> *** 4437,4442 ****
> --- 4437,4557 ----
> </para>
>
> <table>
> + <title>Configuration Settings Information Functions</title>
> + <tgroup cols="3">
> + <thead>
> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
> + </thead>
> +
> + <tbody>
> + <row>
> + <entry>
> + <function>current_setting</function>(<parameter>setting_name</parameter>)
> + </entry>
> + <entry><type>text</type></entry>
> + <entry>value of current setting</entry>
> + </row>
> + <row>
> + <entry>
> + <function>set_config(<parameter>setting_name</parameter>,
> + <parameter>new_value</parameter>,
> + <parameter>is_local</parameter>)</function>
> + </entry>
> + <entry><type>text</type></entry>
> + <entry>new value of current setting</entry>
> + </row>
> + </tbody>
> + </tgroup>
> + </table>
> +
> + <indexterm zone="functions-misc">
> + <primary>setting</primary>
> + <secondary>current</secondary>
> + </indexterm>
> +
> + <indexterm zone="functions-misc">
> + <primary>setting</primary>
> + <secondary>set</secondary>
> + </indexterm>
> +
> + <para>
> + The <function>current_setting</function> is used to obtain the current
> + value of the <parameter>setting_name</parameter> setting, as a query
> + result. It is the equivalent to the SQL <command>SHOW</command> command.
> + For example:
> + <programlisting>
> + select current_setting('DateStyle');
> + current_setting
> + ---------------------------------------
> + ISO with US (NonEuropean) conventions
> + (1 row)
> + </programlisting>
> + </para>
> +
> + <para>
> + <function>set_config</function> allows the <parameter>setting_name
> + </parameter> setting to be changed to <parameter>new_value</parameter>.
> + If <parameter>is_local</parameter> is set to <literal>true</literal>,
> + the new value will only apply to the current transaction. If you want
> + the new value to apply for the current session, use
> + <literal>false</literal> instead. It is the equivalent to the SQL
> + <command>SET</command> command. For example:
> + <programlisting>
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + on
> + (1 row)
> +
> + select set_config('show_query_stats','off','f');
> + set_config
> + ------------
> + off
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> +
> + select set_config('show_query_stats','on','t');
> + set_config
> + ------------
> + on
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> +
> + BEGIN;
> + BEGIN
> + select set_config('show_query_stats','on','t');
> + set_config
> + ------------
> + on
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + on
> + (1 row)
> +
> + COMMIT;
> + COMMIT
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> + </programlisting>
> + </para>
> +
> + <table>
> <title>Access Privilege Inquiry Functions</title>
> <tgroup cols="3">
> <thead>
> Index: doc/src/sgml/ref/set.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/ref/set.sgml,v
> retrieving revision 1.62
> diff -c -r1.62 set.sgml
> *** doc/src/sgml/ref/set.sgml 11 Jun 2002 15:41:30 -0000 1.62
> --- doc/src/sgml/ref/set.sgml 20 Jul 2002 22:06:25 -0000
> ***************
> *** 495,500 ****
> --- 495,510 ----
> </para>
> </refsect2>
> </refsect1>
> +
> + <refsect1>
> + <title>See Also</title>
> +
> + <para>
> + The function <function>set_config</function> provides the equivalent
> + capability. See <citetitle>Miscellaneous Functions</citetitle> in the
> + <citetitle>PostgreSQL User's Guide</citetitle>.
> + </para>
> + </refsect1>
> </refentry>
>
> <!-- Keep this comment at the end of the file
> Index: doc/src/sgml/ref/show.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/ref/show.sgml,v
> retrieving revision 1.17
> diff -c -r1.17 show.sgml
> *** doc/src/sgml/ref/show.sgml 17 May 2002 01:19:16 -0000 1.17
> --- doc/src/sgml/ref/show.sgml 20 Jul 2002 22:06:37 -0000
> ***************
> *** 83,89 ****
>
> <screen>
> SHOW DateStyle;
> ! INFO: DateStyle is ISO with US (NonEuropean) conventions
> </screen>
> </para>
>
> --- 83,92 ----
>
> <screen>
> SHOW DateStyle;
> ! DateStyle
> ! ---------------------------------------
> ! ISO with US (NonEuropean) conventions
> ! (1 row)
> </screen>
> </para>
>
> ***************
> *** 91,99 ****
> Show the current genetic optimizer (<literal>geqo</literal>) setting:
> <screen>
> SHOW GEQO;
> ! INFO: geqo is on
> </screen>
> </para>
> </refsect1>
>
> <refsect1 id="R1-SQL-SHOW-3">
> --- 94,125 ----
> Show the current genetic optimizer (<literal>geqo</literal>) setting:
> <screen>
> SHOW GEQO;
> ! geqo
> ! ------
> ! on
> ! (1 row)
> </screen>
> </para>
> +
> + <para>
> + Show all settings:
> + <screen>
> + SHOW ALL;
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> + </screen>
> + </para>
> +
> </refsect1>
>
> <refsect1 id="R1-SQL-SHOW-3">
> ***************
> *** 102,107 ****
> --- 128,143 ----
> <para>
> The <command>SHOW</command> command is a
> <productname>PostgreSQL</productname> extension.
> + </para>
> + </refsect1>
> +
> + <refsect1>
> + <title>See Also</title>
> +
> + <para>
> + The function <function>current_setting</function> produces equivalent
> + output. See <citetitle>Miscellaneous Functions</citetitle> in the
> + <citetitle>PostgreSQL User's Guide</citetitle>.
> </para>
> </refsect1>
> </refentry>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Later version of patch applied. Thanks.
---------------------------------------------------------------------------
Joe Conway wrote:
> Tom Lane wrote:
> > This patch is lacking documentation additions for the newly-defined
> > functions; not to mention doc updates for the new behavior of SHOW.
> > Please supply...
>
> Here is a doc patch for the SHOW X changes and new config-settings
> functions. If there are no objections, please apply.
>
> Thanks,
>
> Joe
> Index: doc/src/sgml/ref/show.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/ref/show.sgml,v
> retrieving revision 1.17
> diff -c -r1.17 show.sgml
> *** doc/src/sgml/ref/show.sgml 17 May 2002 01:19:16 -0000 1.17
> --- doc/src/sgml/ref/show.sgml 20 Jul 2002 16:54:21 -0000
> ***************
> *** 83,89 ****
>
> <screen>
> SHOW DateStyle;
> ! INFO: DateStyle is ISO with US (NonEuropean) conventions
> </screen>
> </para>
>
> --- 83,92 ----
>
> <screen>
> SHOW DateStyle;
> ! DateStyle
> ! ---------------------------------------
> ! ISO with US (NonEuropean) conventions
> ! (1 row)
> </screen>
> </para>
>
> ***************
> *** 91,99 ****
> Show the current genetic optimizer (<literal>geqo</literal>) setting:
> <screen>
> SHOW GEQO;
> ! INFO: geqo is on
> </screen>
> </para>
> </refsect1>
>
> <refsect1 id="R1-SQL-SHOW-3">
> --- 94,125 ----
> Show the current genetic optimizer (<literal>geqo</literal>) setting:
> <screen>
> SHOW GEQO;
> ! geqo
> ! ------
> ! on
> ! (1 row)
> </screen>
> </para>
> +
> + <para>
> + Show all settings:
> + <screen>
> + SHOW ALL;
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> + </screen>
> + </para>
> +
> </refsect1>
>
> <refsect1 id="R1-SQL-SHOW-3">
> Index: doc/src/sgml/func.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/func.sgml,v
> retrieving revision 1.104
> diff -c -r1.104 func.sgml
> *** doc/src/sgml/func.sgml 24 Jun 2002 22:17:01 -0000 1.104
> --- doc/src/sgml/func.sgml 20 Jul 2002 20:34:04 -0000
> ***************
> *** 4437,4442 ****
> --- 4437,4555 ----
> </para>
>
> <table>
> + <title>Configuration Settings Information Functions</title>
> + <tgroup cols="3">
> + <thead>
> + <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
> + </thead>
> +
> + <tbody>
> + <row>
> + <entry>
> + <function>current_setting</function>(<parameter>setting_name</parameter>)
> + </entry>
> + <entry><type>text</type></entry>
> + <entry>value of current setting</entry>
> + </row>
> + <row>
> + <entry>
> + <function>set_config(<parameter>setting_name</parameter>,
> + <parameter>new_value</parameter>,
> + <parameter>is_local</parameter>)</function>
> + </entry>
> + <entry><type>text</type></entry>
> + <entry>new value of current setting</entry>
> + </row>
> + </tbody>
> + </tgroup>
> + </table>
> +
> + <indexterm zone="functions-misc">
> + <primary>setting</primary>
> + <secondary>current</secondary>
> + </indexterm>
> +
> + <indexterm zone="functions-misc">
> + <primary>setting</primary>
> + <secondary>set</secondary>
> + </indexterm>
> +
> + <para>
> + The <function>current_setting</function> is used to obtain the current
> + value of the <parameter>setting_name</parameter> setting, as a query
> + result. For example:
> + <programlisting>
> + select current_setting('DateStyle');
> + current_setting
> + ---------------------------------------
> + ISO with US (NonEuropean) conventions
> + (1 row)
> + </programlisting>
> + </para>
> +
> + <para>
> + <function>set_config</function> allows the <parameter>setting_name
> + </parameter> setting to be changed to <parameter>new_value</parameter>.
> + If <parameter>is_local</parameter> is set to <literal>true</literal>,
> + the new value will only apply to the current transaction. If you want
> + the new value to apply for the current session, use <literal>false</literal>
> + instead. For example:
> + <programlisting>
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + on
> + (1 row)
> +
> + select set_config('show_query_stats','off','f');
> + set_config
> + ------------
> + off
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> +
> + select set_config('show_query_stats','on','t');
> + set_config
> + ------------
> + on
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> +
> + BEGIN;
> + BEGIN
> + select set_config('show_query_stats','on','t');
> + set_config
> + ------------
> + on
> + (1 row)
> +
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + on
> + (1 row)
> +
> + COMMIT;
> + COMMIT
> + SHOW show_query_stats;
> + show_query_stats
> + ------------------
> + off
> + (1 row)
> + </programlisting>
> + </para>
> +
> + <table>
> <title>Access Privilege Inquiry Functions</title>
> <tgroup cols="3">
> <thead>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026