Re: [Fwd: contrib/showguc (was Re: [HACKERS] revised sample - Mailing list pgsql-patches

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@joeconway.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@joeconway.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@joeconway.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@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