Re: [Fwd: contrib/showguc (was Re: [HACKERS] revised sample

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:
> > Well, we're not doing that; and I see no good reason to make the thing
> > be a builtin function at all. Since it's just an example, it can very
> > well be a contrib item with a creation script. Probably *should* be,
> > in fact, because dynamically created functions are what other people are
> > going to be building; an example of how to do it as a builtin function
> > isn't as helpful.
>
> Here is a patch for contrib/showguc. It can serve as a reference
> implementation for a C function which returns setof composite. It
> required some small changes in guc.c and guc.h so that the number of GUC
> variables, and their values, could be accessed. Example usage as shown
> below:
>
> test=# select * from show_all_vars() where varname = 'wal_sync_method';
> varname | varval
> -----------------+-----------
> wal_sync_method | fdatasync
> (1 row)
>
> test=# select show_var('wal_sync_method');
> show_var
> -----------
> fdatasync
> (1 row)
>
>
> show_var() is neither composite nor set returning, but it seemed like a
> worthwhile addition. Please apply if there are no objections.
>
> Thanks,
>
> Joe
>

> Index: contrib/showguc/Makefile
> ===================================================================
> RCS file: contrib/showguc/Makefile
> diff -N contrib/showguc/Makefile
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/showguc/Makefile 27 May 2002 00:24:44 -0000
> ***************
> *** 0 ****
> --- 1,9 ----
> + subdir = contrib/showguc
> + top_builddir = ../..
> + include $(top_builddir)/src/Makefile.global
> +
> + MODULES = showguc
> + DATA_built = showguc.sql
> + DOCS = README.showguc
> +
> + include $(top_srcdir)/contrib/contrib-global.mk
> Index: contrib/showguc/README.showguc
> ===================================================================
> RCS file: contrib/showguc/README.showguc
> diff -N contrib/showguc/README.showguc
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/showguc/README.showguc 10 Jun 2002 00:16:48 -0000
> ***************
> *** 0 ****
> --- 1,105 ----
> + /*
> + * showguc
> + *
> + * Sample to demonstrate a C function which returns setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + Version 0.1 (9 June, 2002):
> + First release
> +
> + Release Notes:
> +
> + Version 0.1
> + - initial release
> +
> + Installation:
> + Place these files in a directory called 'showguc' under 'contrib' in the PostgreSQL source tree. Then run:
> +
> + make
> + make install
> +
> + You can use showguc.sql to create the functions in your database of choice, e.g.
> +
> + psql -U postgres template1 < showguc.sql
> +
> + installs following functions into database template1:
> +
> + show_all_vars() - returns all GUC variables
> + show_var(text) - returns value of the requested GUC variable
> +
> + Documentation
> + ==================================================================
> + Name
> +
> + show_all_vars() - returns all GUC variables
> +
> + Synopsis
> +
> + show_all_vars()
> +
> + Inputs
> +
> + none
> +
> + Outputs
> +
> + Returns setof __gucvar, where __gucvar is (varname TEXT, varval TEXT). All
> + GUC variables displayed by SHOW ALL are returned as a set.
> +
> + Example usage
> +
> + test=# select * from show_all_vars() where varname = 'wal_sync_method';
> + varname | varval
> + -----------------+-----------
> + wal_sync_method | fdatasync
> + (1 row)
> +
> + ==================================================================
> + Name
> +
> + show_var(text varname) - returns value of GUC variable varname
> +
> + Synopsis
> +
> + show_var(varname)
> +
> + Inputs
> +
> + varname
> + The name of a GUC variable
> +
> + Outputs
> +
> + Returns the current value of varname.
> +
> + Example usage
> +
> + test=# select show_var('wal_sync_method');
> + show_var
> + -----------
> + fdatasync
> + (1 row)
> +
> + ==================================================================
> + -- Joe Conway
> +
> Index: contrib/showguc/showguc.c
> ===================================================================
> RCS file: contrib/showguc/showguc.c
> diff -N contrib/showguc/showguc.c
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/showguc/showguc.c 10 Jun 2002 00:02:14 -0000
> ***************
> *** 0 ****
> --- 1,152 ----
> + /*
> + * showguc
> + *
> + * Sample to demonstrate a C function which returns setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + #include "postgres.h"
> +
> + #include "fmgr.h"
> + #include "funcapi.h"
> + #include "utils/builtins.h"
> + #include "utils/guc.h"
> +
> + #include "showguc.h"
> +
> + /*
> + * showguc_all - equiv to SHOW ALL command but implemented as
> + * an SRF.
> + */
> + PG_FUNCTION_INFO_V1(showguc_all);
> + Datum
> + showguc_all(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTPASS())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /*
> + * Build a tuple description for a pg__guc tuple
> + */
> + tupdesc = RelationNameGetTupleDesc("__gucvar");
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = GetNumGUCConfigOptions();
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP(funcctx);
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + attinmeta = funcctx->attinmeta;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + char *varname;
> + char *varval;
> + char **values;
> + HeapTuple tuple;
> + Datum result;
> +
> + /*
> + * Get the next GUC variable name and value
> + */
> + varval = GetGUCConfigOptionNum(call_cntr, &varname);
> +
> + /*
> + * Prepare a values array for storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" functions.
> + */
> + values = (char **) palloc(2 * sizeof(char *));
> + values[0] = varname;
> + values[1] = varval;
> +
> + /* build a tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + pfree(varname);
> + pfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> +
> + /*
> + * showguc_name - equiv to SHOW X command but implemented as
> + * a function.
> + */
> + PG_FUNCTION_INFO_V1(showguc_name);
> + Datum
> + showguc_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 = GetGUCConfigOptionName(varname);
> +
> + /* Convert to text */
> + result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(varval)));
> +
> + /* return it */
> + PG_RETURN_TEXT_P(result_text);
> + }
> +
> Index: contrib/showguc/showguc.h
> ===================================================================
> RCS file: contrib/showguc/showguc.h
> diff -N contrib/showguc/showguc.h
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/showguc/showguc.h 10 Jun 2002 00:01:02 -0000
> ***************
> *** 0 ****
> --- 1,37 ----
> + /*
> + * showguc
> + *
> + * Sample to demonstrate a C function which returns setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> +
> + #ifndef SHOWGUC_H
> + #define SHOWGUC_H
> +
> + /*
> + * External declarations
> + */
> + extern Datum showguc_all(PG_FUNCTION_ARGS);
> + extern Datum showguc_name(PG_FUNCTION_ARGS);
> +
> + #endif /* SHOWGUC_H */
> Index: contrib/showguc/showguc.sql.in
> ===================================================================
> RCS file: contrib/showguc/showguc.sql.in
> diff -N contrib/showguc/showguc.sql.in
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/showguc/showguc.sql.in 10 Jun 2002 00:03:13 -0000
> ***************
> *** 0 ****
> --- 1,10 ----
> + CREATE VIEW __gucvar AS
> + SELECT
> + ''::TEXT AS varname,
> + ''::TEXT AS varval;
> +
> + CREATE OR REPLACE FUNCTION show_all_vars() RETURNS setof __gucvar
> + AS 'MODULE_PATHNAME','showguc_all' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION show_var(text) RETURNS text
> + AS 'MODULE_PATHNAME','showguc_name' LANGUAGE 'c' STABLE STRICT;
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
> retrieving revision 1.69
> diff -c -r1.69 guc.c
> *** src/backend/utils/misc/guc.c 17 May 2002 20:32:29 -0000 1.69
> --- src/backend/utils/misc/guc.c 9 Jun 2002 22:51:45 -0000
> ***************
> *** 824,830 ****
>
>
> static int guc_var_compare(const void *a, const void *b);
> ! static void _ShowOption(struct config_generic *record);
>
>
> /*
> --- 824,830 ----
>
>
> static int guc_var_compare(const void *a, const void *b);
> ! static char *_ShowOption(struct config_generic *record);
>
>
> /*
> ***************
> *** 2204,2215 ****
> 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);
> }
>
> /*
> --- 2204,2221 ----
> 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);
> ! }
> }
>
> /*
> ***************
> *** 2219,2239 ****
> 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)
> {
> --- 2225,2295 ----
> 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 *
> ! GetGUCConfigOptionName(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 *
> ! GetGUCConfigOptionNum(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
> ! GetNumGUCConfigOptions(void)
> ! {
> ! return num_guc_variables;
> ! }
> !
> ! static char *
> _ShowOption(struct config_generic *record)
> {
> char buffer[256];
> const char *val;
> + char *retval;
>
> switch (record->vartype)
> {
> ***************
> *** 2297,2303 ****
> break;
> }
>
> ! elog(INFO, "%s is %s", record->name, val);
> }
>
>
> --- 2353,2361 ----
> break;
> }
>
> ! retval = pstrdup(val);
> !
> ! return retval;
> }
>
>
> 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 9 Jun 2002 22:45:20 -0000
> ***************
> *** 86,91 ****
> --- 86,94 ----
> bool isLocal, bool DoIt);
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> + extern char *GetGUCConfigOptionName(const char *name);
> + extern char *GetGUCConfigOptionNum(int varnum, char **varname);
> + extern int GetNumGUCConfigOptions(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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026