The attached patch is my first pass at a sample C function returning
setof composite. It is a clone of SHOW ALL as an SRF. For the moment,
the function is implemented as contrib/showguc, although a few minor
changes to guc.c and guc.h were required to support it.
I wanted to post it to HACKERS because several people asked for such an
example. I also wanted to see if there was any interest in having this
folded into the backend, either in addition to, or in place of, the
current SHOW ALL functionality.
Comments?
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 26 May 2002 00:24:40 -0000
***************
*** 0 ****
--- 1,11 ----
+ # $Header: /opt/src/cvs/pgsql/contrib/fuzzystrmatch/Makefile,v 1.2 2001/09/06
+10:49:29 petere Exp $
+
+ 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 26 May 2002 05:46:57 -0000
***************
*** 0 ****
--- 1,77 ----
+ /*
+ * showguc
+ *
+ * Sample function to demonstrate a C function which returns setof composite.
+ *
+ * Copyright 2002 by PostgreSQL Global Development Group
+ * Cloned from src/backend/utils/misc/guc.c which was written by Peter Eisentraut
+ * <[EMAIL PROTECTED]>, and modified to suit.
+ *
+ * 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 (25 May, 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:
+
+ showvars() - returns all GUC variables
+
+ Documentation
+ ==================================================================
+ Name
+
+ showvars() - returns all GUC variables
+
+ Synopsis
+
+ showvars()
+
+ 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
+
+ select showvars();
+
+ ==================================================================
+ -- 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 26 May 2002 05:43:08 -0000
***************
*** 0 ****
--- 1,476 ----
+ /*--------------------------------------------------------------------
+ * showguc.c
+ *
+ * Sample function to demonstrate a C function which returns setof composite.
+ *
+ * Copyright 2002 by PostgreSQL Global Development Group
+ * Cloned from src/backend/utils/misc/guc.c which was written by Peter Eisentraut
+ * <[EMAIL PROTECTED]>, and modified to suit.
+ *--------------------------------------------------------------------
+ */
+
+ #include "postgres.h"
+
+ #include <errno.h>
+ #include <float.h>
+ #include <limits.h>
+ #include <unistd.h>
+
+ #include "fmgr.h"
+ #include "showguc.h"
+
+ #include "utils/guc.h"
+ #include "access/xlog.h"
+ #include "access/heapam.h"
+ #include "catalog/namespace.h"
+ #include "catalog/pg_type.h"
+ #include "commands/async.h"
+ #include "commands/variable.h"
+ #include "executor/executor.h"
+ #include "libpq/auth.h"
+ #include "libpq/pqcomm.h"
+ #include "miscadmin.h"
+ #include "optimizer/cost.h"
+ #include "optimizer/geqo.h"
+ #include "optimizer/paths.h"
+ #include "optimizer/planmain.h"
+ #include "parser/parse_expr.h"
+ #include "storage/fd.h"
+ #include "storage/freespace.h"
+ #include "storage/lock.h"
+ #include "storage/proc.h"
+ #include "tcop/tcopprot.h"
+ #include "utils/array.h"
+ #include "utils/builtins.h"
+ #include "utils/datetime.h"
+ #include "utils/elog.h"
+ #include "utils/pg_locale.h"
+ #include "pgstat.h"
+
+
+ /* XXX these should be in other modules' header files */
+ extern bool Log_connections;
+ extern int PreAuthDelay;
+ extern int AuthenticationTimeout;
+ extern int CheckPointTimeout;
+ extern int CommitDelay;
+ extern int CommitSiblings;
+ extern bool FixBTree;
+
+ #ifdef HAVE_SYSLOG
+ extern char *Syslog_facility;
+ extern char *Syslog_ident;
+
+ #endif
+
+ /*
+ * Debugging options
+ */
+ #ifdef USE_ASSERT_CHECKING
+ bool assert_enabled = true;
+ #endif
+ bool Debug_print_query = false;
+ bool Debug_print_plan = false;
+ bool Debug_print_parse = false;
+ bool Debug_print_rewritten = false;
+ bool Debug_pretty_print = false;
+
+ bool Show_parser_stats = false;
+ bool Show_planner_stats = false;
+ bool Show_executor_stats = false;
+ bool Show_query_stats = false; /* this is sort of all three above
+ *
+together */
+ bool Show_btree_build_stats = false;
+
+ bool Explain_pretty_print = true;
+
+ bool SQL_inheritance = true;
+
+ bool Australian_timezones = false;
+
+ bool Password_encryption = false;
+
+ #ifndef PG_KRB_SRVTAB
+ #define PG_KRB_SRVTAB ""
+ #endif
+
+ static char *GetNextGUCConfig(int varnum, char **varname);
+ static char *_GetOption(struct config_generic *record);
+ static TupleTableSlot *get_slot(char *relname, Datum *values);
+
+ /*
+ * Declarations for GUC tables
+ *
+ * See src/backend/utils/misc/README for design notes.
+ */
+ enum config_type
+ {
+ PGC_BOOL,
+ PGC_INT,
+ PGC_REAL,
+ PGC_STRING
+ };
+
+ /* Generic fields applicable to all types of variables */
+ struct config_generic
+ {
+ /* constant fields, must be set correctly in initial value: */
+ const char *name; /* name of variable - MUST BE FIRST */
+ GucContext context; /* context required to set the
+variable */
+ int flags; /* flag bits, see below */
+ /* variable fields, initialized at runtime: */
+ enum config_type vartype; /* type of variable (set only at startup) */
+ int status; /* status bits, see below */
+ GucSource reset_source; /* source of the reset_value */
+ GucSource session_source; /* source of the session_value */
+ GucSource tentative_source; /* source of the tentative_value */
+ GucSource source; /* source of the current actual value
+*/
+ };
+
+ /* bit values in flags field */
+ #define GUC_LIST_INPUT 0x0001 /* input can be list format */
+ #define GUC_LIST_QUOTE 0x0002 /* double-quote list elements */
+ #define GUC_NO_SHOW_ALL 0x0004 /* exclude from SHOW ALL */
+ #define GUC_NO_RESET_ALL 0x0008 /* exclude from RESET ALL */
+
+ /* bit values in status field */
+ #define GUC_HAVE_TENTATIVE 0x0001 /* tentative value is defined */
+ #define GUC_HAVE_LOCAL 0x0002 /* a SET LOCAL has been executed */
+
+
+ /* GUC records for specific variable types */
+
+ struct config_bool
+ {
+ struct config_generic gen;
+ /* these fields must be set correctly in initial value: */
+ /* (all but reset_val are constants) */
+ bool *variable;
+ bool reset_val;
+ bool (*assign_hook) (bool newval, bool doit, bool interactive);
+ const char *(*show_hook) (void);
+ /* variable fields, initialized at runtime: */
+ bool session_val;
+ bool tentative_val;
+ };
+
+ struct config_int
+ {
+ struct config_generic gen;
+ /* these fields must be set correctly in initial value: */
+ /* (all but reset_val are constants) */
+ int *variable;
+ int reset_val;
+ int min;
+ int max;
+ bool (*assign_hook) (int newval, bool doit, bool interactive);
+ const char *(*show_hook) (void);
+ /* variable fields, initialized at runtime: */
+ int session_val;
+ int tentative_val;
+ };
+
+ struct config_real
+ {
+ struct config_generic gen;
+ /* these fields must be set correctly in initial value: */
+ /* (all but reset_val are constants) */
+ double *variable;
+ double reset_val;
+ double min;
+ double max;
+ bool (*assign_hook) (double newval, bool doit, bool interactive);
+ const char *(*show_hook) (void);
+ /* variable fields, initialized at runtime: */
+ double session_val;
+ double tentative_val;
+ };
+
+ struct config_string
+ {
+ struct config_generic gen;
+ /* these fields must be set correctly in initial value: */
+ /* (all are constants) */
+ char **variable;
+ const char *boot_val;
+ const char *(*assign_hook) (const char *newval, bool doit, bool interactive);
+ const char *(*show_hook) (void);
+ /* variable fields, initialized at runtime: */
+ char *reset_val;
+ char *session_val;
+ char *tentative_val;
+ };
+
+ /* Macros for freeing malloc'd pointers only if appropriate to do so */
+ /* Some of these tests are probably redundant, but be safe ... */
+ #define SET_STRING_VARIABLE(rec, newval) \
+ do { \
+ if (*(rec)->variable && \
+ *(rec)->variable != (rec)->reset_val && \
+ *(rec)->variable != (rec)->session_val && \
+ *(rec)->variable != (rec)->tentative_val) \
+ free(*(rec)->variable); \
+ *(rec)->variable = (newval); \
+ } while (0)
+ #define SET_STRING_RESET_VAL(rec, newval) \
+ do { \
+ if ((rec)->reset_val && \
+ (rec)->reset_val != *(rec)->variable && \
+ (rec)->reset_val != (rec)->session_val && \
+ (rec)->reset_val != (rec)->tentative_val) \
+ free((rec)->reset_val); \
+ (rec)->reset_val = (newval); \
+ } while (0)
+ #define SET_STRING_SESSION_VAL(rec, newval) \
+ do { \
+ if ((rec)->session_val && \
+ (rec)->session_val != *(rec)->variable && \
+ (rec)->session_val != (rec)->reset_val && \
+ (rec)->session_val != (rec)->tentative_val) \
+ free((rec)->session_val); \
+ (rec)->session_val = (newval); \
+ } while (0)
+ #define SET_STRING_TENTATIVE_VAL(rec, newval) \
+ do { \
+ if ((rec)->tentative_val && \
+ (rec)->tentative_val != *(rec)->variable && \
+ (rec)->tentative_val != (rec)->reset_val && \
+ (rec)->tentative_val != (rec)->session_val) \
+ free((rec)->tentative_val); \
+ (rec)->tentative_val = (newval); \
+ } while (0)
+
+
+ /*
+ * Actual lookup of variables is done through this single, sorted array.
+ */
+ static struct config_generic **guc_variables;
+ static int num_guc_variables;
+
+
+ /*
+ * SHOW command
+ */
+ PG_FUNCTION_INFO_V1(showguc);
+
+ Datum
+ showguc(PG_FUNCTION_ARGS)
+ {
+ char *relname = "__gucvar";
+ char *varname = NULL;
+ char *varval;
+ Datum *values;
+ ReturnSetInfo *rsi;
+ TupleTableSlot *slot;
+ int *varnum;
+
+ guc_variables = get_guc_variables();
+ num_guc_variables = get_num_guc_variables();
+
+ if (fcinfo->resultinfo == NULL || !IsA(fcinfo->resultinfo, ReturnSetInfo))
+ elog(ERROR, "showguc: function called in context that does not accept
+a set result");
+
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ varnum = (int *) palloc(sizeof(int));
+ *varnum = 0;
+ fcinfo->flinfo->fn_extra = varnum;
+ }
+ else
+ {
+ varnum = fcinfo->flinfo->fn_extra;
+ (*varnum)++;
+ }
+
+ if (*varnum < num_guc_variables)
+ {
+ varval = GetNextGUCConfig(*varnum, &varname);
+
+ rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+ rsi->isDone = ExprMultipleResult;
+
+ values = (Datum *) palloc(2 * sizeof(Datum));
+ if (varname != NULL)
+ values[0] = DirectFunctionCall1(textin,
+CStringGetDatum(varname));
+ else
+ values[0] = PointerGetDatum(NULL);
+
+ if (varval != NULL)
+ values[1] = DirectFunctionCall1(textin,
+CStringGetDatum(varval));
+ else
+ values[1] = PointerGetDatum(NULL);
+
+ slot = get_slot(relname, values);
+
+ pfree(values);
+
+ PG_RETURN_POINTER(slot);
+ }
+ else
+ {
+ /* All done! */
+ fcinfo->flinfo->fn_extra = NULL;
+
+ rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+ rsi->isDone = ExprEndResult;
+
+ slot = NULL;
+ PG_RETURN_POINTER(slot);
+ }
+ }
+
+ /* internal functions */
+
+ /*
+ * SHOW ALL command
+ */
+ static char *
+ GetNextGUCConfig(int varnum, char **varname)
+ {
+ struct config_generic *conf = guc_variables[varnum];
+
+ *varname = pstrdup(conf->name);
+
+ if ((conf->flags & GUC_NO_SHOW_ALL) == 0)
+ return _GetOption(conf);
+ else
+ return NULL;
+
+ }
+
+ static char *
+ _GetOption(struct config_generic *record)
+ {
+ char buffer[256];
+ const char *val;
+
+ switch (record->vartype)
+ {
+ case PGC_BOOL:
+ {
+ struct config_bool *conf = (struct config_bool *)
+record;
+
+ if (conf->show_hook)
+ val = (*conf->show_hook) ();
+ else
+ val = *conf->variable ? "on" : "off";
+ }
+ break;
+
+ case PGC_INT:
+ {
+ struct config_int *conf = (struct config_int *) record;
+
+ if (conf->show_hook)
+ val = (*conf->show_hook) ();
+ else
+ {
+ snprintf(buffer, sizeof(buffer), "%d",
+ *conf->variable);
+ val = buffer;
+ }
+ }
+ break;
+
+ case PGC_REAL:
+ {
+ struct config_real *conf = (struct config_real *)
+record;
+
+ if (conf->show_hook)
+ val = (*conf->show_hook) ();
+ else
+ {
+ snprintf(buffer, sizeof(buffer), "%g",
+ *conf->variable);
+ val = buffer;
+ }
+ }
+ break;
+
+ case PGC_STRING:
+ {
+ struct config_string *conf = (struct config_string *)
+record;
+
+ if (conf->show_hook)
+ val = (*conf->show_hook) ();
+ else if (*conf->variable && **conf->variable)
+ val = *conf->variable;
+ else
+ val = "unset";
+ }
+ break;
+
+ default:
+ /* just to keep compiler quiet */
+ val = "???";
+ break;
+ }
+
+ return pstrdup(val);
+ }
+
+
+ static TupleTableSlot *
+ get_slot(char *relname, Datum *values)
+ {
+ TupleTableSlot *slot;
+ Oid relid;
+ Relation rel;
+ TupleDesc tupdesc;
+ int natts;
+ HeapTuple tuple;
+ char *nulls;
+ int i;
+
+ /*
+ * Make a standalone slot
+ */
+ slot = MakeTupleTableSlot();
+
+ /*
+ * Open relation and get the tuple description
+ */
+ relid = RelnameGetRelid(relname);
+ rel = relation_open(relid, AccessShareLock);
+ tupdesc = CreateTupleDescCopy(rel->rd_att);
+ natts = tupdesc->natts;
+ relation_close(rel, AccessShareLock);
+
+ /*
+ * Bind the tuple description to the slot
+ */
+ ExecSetSlotDescriptor(slot, tupdesc, true);
+
+ /*
+ * Form a tuple
+ */
+ nulls = (char *) palloc(natts * sizeof(char));
+ for (i = 0; i < natts; i++)
+ {
+ if (DatumGetPointer(values[i]) != NULL)
+ nulls[i] = ' ';
+ else
+ nulls[i] = 'n';
+ }
+ tuple = heap_formtuple(tupdesc, values, nulls);
+
+ /*
+ * Save the tuple in the tuple slot
+ */
+ slot = ExecStoreTuple(tuple, /* tuple to store */
+ slot, /*
+slot to store in */
+ InvalidBuffer, /* buffer
+associated with
+
+ * this tuple */
+ true); /*
+pfree this pointer */
+
+ /*
+ * Clean up
+ */
+ pfree(nulls);
+
+ /*
+ * Return the slot!
+ */
+ return slot;
+ }
+
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 26 May 2002 02:06:52 -0000
***************
*** 0 ****
--- 1,15 ----
+ /*
+ * showguc.c
+ *
+ * Sample function to demonstrate a C function which returns setof composite.
+ *
+ * Copyright 2002 by PostgreSQL Global Development Group
+ * Cloned from src/backend/utils/misc/guc.c which was written by Peter Eisentraut
+ * <[EMAIL PROTECTED]>, and modified to suit.
+ */
+ #ifndef SHOWGUC_H
+ #define SHOWGUC_H
+
+ extern Datum showguc(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 26 May 2002 05:36:18 -0000
***************
*** 0 ****
--- 1,7 ----
+ CREATE TABLE __gucvar(
+ varname TEXT,
+ varval TEXT
+ );
+
+ CREATE FUNCTION showvars() RETURNS setof __gucvar
+ AS 'MODULE_PATHNAME','showguc' 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 26 May 2002 02:20:26 -0000
***************
*** 2539,2541 ****
--- 2539,2554 ----
return newarray;
}
+
+ struct config_generic **
+ get_guc_variables(void)
+ {
+ return guc_variables;
+ }
+
+ int
+ get_num_guc_variables(void)
+ {
+ return num_guc_variables;
+ }
+
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 26 May 2002 02:21:00 -0000
***************
*** 97,102 ****
--- 97,105 ----
extern ArrayType *GUCArrayAdd(ArrayType *array, const char *name, const char *value);
extern ArrayType *GUCArrayDelete(ArrayType *array, const char *name);
+ extern struct config_generic **get_guc_variables(void);
+ extern int get_num_guc_variables(void);
+
extern bool Debug_print_query;
extern bool Debug_print_plan;
extern bool Debug_print_parse;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
