[PATCH] "\ef <function>" in psql
Refactoring pg_dump was more work than I had time to do right now, and I
wanted \ef to work, so I hacked up the attached (by copying dumpFunc and
its dependencies to src/bin/psql/dumpfunc.[ch]).
-- ams
Attachments:
edit.difftext/x-diff; charset=us-asciiDownload
*** a/src/bin/psql/Makefile
--- b/src/bin/psql/Makefile
***************
*** 21,27 **** override CPPFLAGS := -I$(srcdir) -I$(libpq_srcdir) -I$(top_srcdir)/src/bin/pg_du
OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
startup.o prompt.o variables.o large_obj.o print.o describe.o \
! psqlscan.o tab-complete.o mbprint.o dumputils.o $(WIN32RES)
EXTRA_OBJS = $(top_builddir)/src/backend/parser/keywords.o
--- 21,27 ----
OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
startup.o prompt.o variables.o large_obj.o print.o describe.o \
! psqlscan.o tab-complete.o mbprint.o dumputils.o dumpfunc.o $(WIN32RES)
EXTRA_OBJS = $(top_builddir)/src/backend/parser/keywords.o
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 38,43 ****
--- 38,44 ----
#include "libpq-fe.h"
#include "pqexpbuffer.h"
#include "dumputils.h"
+ #include "dumpfunc.h"
#include "common.h"
#include "copy.h"
***************
*** 56,62 ****
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
PQExpBuffer query_buf);
! static bool do_edit(const char *filename_arg, PQExpBuffer query_buf);
static bool do_connect(char *dbname, char *user, char *host, char *port);
static bool do_shell(const char *command);
--- 57,64 ----
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
PQExpBuffer query_buf);
! static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
! bool *edited);
static bool do_connect(char *dbname, char *user, char *host, char *port);
static bool do_shell(const char *command);
***************
*** 444,454 **** exec_command(const char *cmd,
expand_tilde(&fname);
if (fname)
canonicalize_path(fname);
! status = do_edit(fname, query_buf) ? PSQL_CMD_NEWEDIT : PSQL_CMD_ERROR;
free(fname);
}
}
/* \echo and \qecho */
else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0)
{
--- 446,521 ----
expand_tilde(&fname);
if (fname)
canonicalize_path(fname);
! if (do_edit(fname, query_buf, NULL))
! status = PSQL_CMD_NEWEDIT;
! else
! status = PSQL_CMD_ERROR;
free(fname);
}
}
+ /*
+ * \ef -- edit the named function in $EDITOR.
+ */
+
+ else if (strcmp(cmd, "ef") == 0)
+ {
+ Oid foid;
+ char *func;
+
+ func = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
+ if (!func)
+ {
+ psql_error("no function name specified\n");
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!lookup_function_oid(pset.db, func, &foid))
+ {
+ psql_error(PQerrorMessage(pset.db));
+ status = PSQL_CMD_ERROR;
+ }
+ else {
+ termPQExpBuffer(query_buf);
+ if (foid)
+ {
+ char *s = create_or_replace_function_text(pset.db, foid);
+ if (s)
+ {
+ appendPQExpBufferStr(query_buf, s);
+ free(s);
+ }
+ else
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ {
+ printfPQExpBuffer(query_buf,
+ "CREATE FUNCTION %s%s RETURNS ... AS $$\n"
+ "...\n"
+ "$$ LANGUAGE '...'\n",
+ func, strchr(func,'(') ? "" : "(...)" );
+ }
+ }
+
+ if (status != PSQL_CMD_ERROR)
+ {
+ bool edited = false;
+ if (!do_edit(0, query_buf, &edited))
+ {
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!edited)
+ {
+ printf("No changes\n");
+ }
+ else
+ {
+ status = PSQL_CMD_SEND;
+ }
+ free(func);
+ }
+ }
+
/* \echo and \qecho */
else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0)
{
***************
*** 1410,1416 **** editFile(const char *fname)
/* call this one */
static bool
! do_edit(const char *filename_arg, PQExpBuffer query_buf)
{
char fnametmp[MAXPGPATH];
FILE *stream = NULL;
--- 1477,1483 ----
/* call this one */
static bool
! do_edit(const char *filename_arg, PQExpBuffer query_buf, bool *edited)
{
char fnametmp[MAXPGPATH];
FILE *stream = NULL;
***************
*** 1532,1537 **** do_edit(const char *filename_arg, PQExpBuffer query_buf)
--- 1599,1608 ----
psql_error("%s: %s\n", fname, strerror(errno));
error = true;
}
+ else if (edited)
+ {
+ *edited = true;
+ }
fclose(stream);
}
*** /dev/null
--- b/src/bin/psql/dumpfunc.c
***************
*** 0 ****
--- 1,496 ----
+ #include "dumpfunc.h"
+
+ #include "libpq-fe.h"
+ #include "pqexpbuffer.h"
+ #include "dumputils.h"
+ #include "common.h"
+ #include "catalog/pg_proc.h"
+
+ #define atooid(x) ((Oid) strtoul((x), NULL, 10))
+
+ /*
+ * This function takes a function description, e.g. "x" or "x(int)", and
+ * issues a query on the given connection to retrieve the function's oid
+ * using a cast to regproc or regprocedure (as appropriate). The result,
+ * if there is one, is stored in the integer pointed to by result, which
+ * is assumed to be non-zero. If there are no results (i.e. the function
+ * does not exist), 0 is stored. The function then returns true.
+ *
+ * If the oid lookup query fails (which it will, for example, when
+ * multiple functions match the given description), it returns false.
+ */
+
+ bool
+ lookup_function_oid(PGconn *conn, const char *desc, Oid *result)
+ {
+ PGresult *res;
+ PQExpBuffer buf;
+
+ buf = createPQExpBuffer();
+ printfPQExpBuffer(buf, "SELECT '%s'::%s::oid",
+ desc, strchr(desc, '(') ? "regprocedure" : "regproc");
+
+ res = PQexec(conn, buf->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+ return false;
+
+ *result = 0;
+ if (PQntuples(res) > 0)
+ *result = atooid(PQgetvalue(res, 0, 0));
+
+ destroyPQExpBuffer(buf);
+ PQclear(res);
+
+ return true;
+ }
+
+ /*
+ * Returns a nicely-formatted type name for the given type oid.
+ * (This is copied from the function in src/bin/pg_dump/pg_dump.c)
+ */
+
+ static char *
+ getFormattedTypeName(PGconn *conn, Oid oid)
+ {
+ char *result;
+ PQExpBuffer query;
+ PGresult *res;
+ int ntups;
+
+ if (oid == 0)
+ return strdup("opaque");
+
+ query = createPQExpBuffer();
+ appendPQExpBuffer(query,
+ "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
+ oid);
+ res = PQexec(conn, query->data);
+
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ const char *err;
+ if (res)
+ err = PQresultErrorMessage(res);
+ else
+ err = PQerrorMessage(conn);
+ psql_error("query: %s, error: %s\n", query->data, err);
+ return NULL;
+ }
+
+ ntups = PQntuples(res);
+ if (ntups != 1)
+ {
+ psql_error("query returned %d rows instead of one: %s\n",
+ ntups, query->data);
+ return NULL;
+ }
+
+ result = strdup(PQgetvalue(res, 0, 0));
+
+ PQclear(res);
+ destroyPQExpBuffer(query);
+
+ return result;
+ }
+
+ /*
+ * Returns the function name and argument list.
+ * (This is also copied from the function in pg_dump.c)
+ */
+
+ static char *
+ format_function_arguments(PGconn *conn, const char *name, int nallargs,
+ Oid *argtypes, char **allargtypes,
+ char **argmodes, char **argnames)
+ {
+ PQExpBufferData fn;
+ int j;
+
+ initPQExpBuffer(&fn);
+ appendPQExpBuffer(&fn, "%s(", fmtId(name));
+ for (j = 0; j < nallargs; j++)
+ {
+ Oid typid;
+ char *typname;
+ const char *argmode;
+ const char *argname;
+
+ typid = allargtypes ? atooid(allargtypes[j]) : argtypes[j];
+ typname = getFormattedTypeName(conn, typid);
+ if (!typname)
+ return NULL;
+
+ if (argmodes)
+ {
+ switch (argmodes[j][0])
+ {
+ case 'i':
+ argmode = "";
+ break;
+ case 'o':
+ argmode = "OUT ";
+ break;
+ case 'b':
+ argmode = "INOUT ";
+ break;
+ default:
+ argmode = "";
+ break;
+ }
+ }
+ else
+ argmode = "";
+
+ argname = argnames ? argnames[j] : (char *) NULL;
+ if (argname && argname[0] == '\0')
+ argname = NULL;
+
+ appendPQExpBuffer(&fn, "%s%s%s%s%s",
+ (j > 0) ? ", " : "",
+ argmode,
+ argname ? fmtId(argname) : "",
+ argname ? " " : "",
+ typname);
+ free(typname);
+ }
+ appendPQExpBuffer(&fn, ")");
+ return fn.data;
+ }
+
+ /*
+ * Parses arraysize oids from str and puts them into array.
+ * (Copied from pg_dump/common.c)
+ */
+
+ static bool
+ parseOidArray(const char *str, Oid *array, int arraysize)
+ {
+ int j,
+ argNum;
+ char temp[100];
+ char s;
+
+ argNum = 0;
+ j = 0;
+ for (;;)
+ {
+ s = *str++;
+ if (s == ' ' || s == '\0')
+ {
+ if (j > 0)
+ {
+ if (argNum >= arraysize)
+ return false;
+ temp[j] = '\0';
+ array[argNum++] = atooid(temp);
+ j = 0;
+ }
+ if (s == '\0')
+ break;
+ }
+ else
+ {
+ if (!(isdigit((unsigned char) s) || s == '-') ||
+ j >= sizeof(temp) - 1)
+ {
+ return false;
+ }
+ temp[j++] = s;
+ }
+ }
+
+ while (argNum < arraysize)
+ array[argNum++] = InvalidOid;
+
+ return true;
+ }
+
+
+ /*
+ * Returns the "CREATE OR REPLACE FUNCTION ..." statement that was used
+ * to create the function with the given oid, which is assumed to be the
+ * result of lookup_function_oid() (i.e. a valid oid from pg_proc).
+ */
+
+ const char *
+ create_or_replace_function_text(PGconn *conn, Oid oid)
+ {
+ int nargs;
+ Oid *argtypes;
+ Oid prorettype;
+ PQExpBuffer query;
+ PQExpBuffer q;
+ PQExpBuffer asPart;
+ PGresult *res;
+ char *funcsig;
+ int ntups;
+ char *proretset;
+ char *prosrc;
+ char *probin;
+ char *proallargtypes;
+ char *proargmodes;
+ char *proargnames;
+ char *provolatile;
+ char *proisstrict;
+ char *prosecdef;
+ char *proconfig;
+ char *procost;
+ char *prorows;
+ char *lanname;
+ char *rettypename;
+ int nallargs;
+ char **allargtypes = NULL;
+ char **argmodes = NULL;
+ char **argnames = NULL;
+ char **configitems = NULL;
+ int nconfigitems = 0;
+ int i;
+ const char *proname;
+ const char *result = NULL;
+
+ q = createPQExpBuffer();
+ asPart = createPQExpBuffer();
+ query = createPQExpBuffer();
+
+ appendPQExpBuffer(query,
+ "SELECT proname, pronargs, proretset, prosrc, probin, "
+ "proargtypes, proallargtypes, prorettype, proargmodes, "
+ "proargnames, provolatile, proisstrict, prosecdef, "
+ "proconfig, procost, prorows, "
+ "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
+ "FROM pg_catalog.pg_proc "
+ "WHERE oid = '%u'::pg_catalog.oid",
+ oid);
+ res = PQexec(conn, query->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ const char *err;
+ if (res)
+ err = PQresultErrorMessage(res);
+ else
+ err = PQerrorMessage(conn);
+ psql_error("query: %s, error: %s\n", query->data, err);
+ goto out;
+ }
+
+ ntups = PQntuples(res);
+ if (ntups != 1)
+ {
+ psql_error("query returned %d rows instead of one: %s\n",
+ ntups, query->data);
+ goto out;
+ }
+
+ proname = PQgetvalue(res, 0, PQfnumber(res, "proname"));
+ prorettype = atooid(PQgetvalue(res, 0, PQfnumber(res, "prorettype")));
+
+ nargs = atoi(PQgetvalue(res, 0, PQfnumber(res, "pronargs")));
+ if (nargs == 0)
+ argtypes = NULL;
+ else
+ {
+ bool ok = false;
+
+ argtypes = (Oid *) malloc(nargs * sizeof(Oid));
+ ok = parseOidArray(PQgetvalue(res, 0, PQfnumber(res, "proargtypes")),
+ argtypes, nargs);
+ if (!ok)
+ {
+ psql_error("Could not parse proargtypes\n");
+ goto out;
+ }
+ }
+
+ proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
+ prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
+ probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
+ proallargtypes = PQgetvalue(res, 0, PQfnumber(res, "proallargtypes"));
+ proargmodes = PQgetvalue(res, 0, PQfnumber(res, "proargmodes"));
+ proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
+ provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
+ proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
+ prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
+ proconfig = PQgetvalue(res, 0, PQfnumber(res, "proconfig"));
+ procost = PQgetvalue(res, 0, PQfnumber(res, "procost"));
+ prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows"));
+ lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
+
+ /*
+ * See backend/commands/define.c for details of how the 'AS' clause is
+ * used.
+ */
+ if (strcmp(probin, "-") != 0)
+ {
+ appendPQExpBuffer(asPart, "AS ");
+ appendStringLiteralConn(asPart, probin, conn);
+ if (strcmp(prosrc, "-") != 0)
+ {
+ appendPQExpBuffer(asPart, ", ");
+ appendStringLiteralDQ(asPart, prosrc, NULL);
+ }
+ }
+ else
+ {
+ if (strcmp(prosrc, "-") != 0)
+ {
+ appendPQExpBuffer(asPart, "AS ");
+ /* with no bin, dollar quote src unconditionally if allowed */
+ appendStringLiteralDQ(asPart, prosrc, NULL);
+ }
+ }
+
+ nallargs = nargs; /* unless we learn different from allargs */
+
+ if (proallargtypes && *proallargtypes)
+ {
+ int nitems = 0;
+
+ if (!parsePGArray(proallargtypes, &allargtypes, &nitems) ||
+ nitems < nargs)
+ {
+ if (allargtypes)
+ free(allargtypes);
+ allargtypes = NULL;
+ }
+ else
+ nallargs = nitems;
+ }
+
+ if (proargmodes && *proargmodes)
+ {
+ int nitems = 0;
+
+ if (!parsePGArray(proargmodes, &argmodes, &nitems) ||
+ nitems != nallargs)
+ {
+ if (argmodes)
+ free(argmodes);
+ argmodes = NULL;
+ }
+ }
+
+ if (proargnames && *proargnames)
+ {
+ int nitems = 0;
+
+ if (!parsePGArray(proargnames, &argnames, &nitems) ||
+ nitems != nallargs)
+ {
+ if (argnames)
+ free(argnames);
+ argnames = NULL;
+ }
+ }
+
+ if (proconfig && *proconfig)
+ {
+ if (!parsePGArray(proconfig, &configitems, &nconfigitems))
+ {
+ if (configitems)
+ free(configitems);
+ configitems = NULL;
+ nconfigitems = 0;
+ }
+ }
+
+ funcsig = format_function_arguments(conn, proname, nallargs, argtypes,
+ allargtypes, argmodes, argnames);
+ rettypename = getFormattedTypeName(conn, prorettype);
+ if (!funcsig || !rettypename)
+ goto out;
+
+ appendPQExpBuffer(q, "CREATE OR REPLACE FUNCTION %s ", funcsig);
+ appendPQExpBuffer(q, "RETURNS %s%s",
+ (proretset[0] == 't') ? "SETOF " : "",
+ rettypename);
+ free(rettypename);
+
+ appendPQExpBuffer(q, "\n LANGUAGE %s", fmtId(lanname));
+ if (provolatile[0] != PROVOLATILE_VOLATILE)
+ {
+ if (provolatile[0] == PROVOLATILE_IMMUTABLE)
+ appendPQExpBuffer(q, " IMMUTABLE");
+ else if (provolatile[0] == PROVOLATILE_STABLE)
+ appendPQExpBuffer(q, " STABLE");
+ else if (provolatile[0] != PROVOLATILE_VOLATILE)
+ {
+ psql_error("unrecognized provolatile value for function \"%s\"\n",
+ proname);
+ goto out;
+ }
+ }
+
+ if (proisstrict[0] == 't')
+ appendPQExpBuffer(q, " STRICT");
+
+ if (prosecdef[0] == 't')
+ appendPQExpBuffer(q, " SECURITY DEFINER");
+
+ /*
+ * COST and ROWS are emitted only if present and not default, so as not to
+ * break backwards-compatibility of the dump without need. Keep this code
+ * in sync with the defaults in functioncmds.c.
+ */
+ if (strcmp(procost, "0") != 0)
+ {
+ if (strcmp(lanname, "internal") == 0 || strcmp(lanname, "c") == 0)
+ {
+ /* default cost is 1 */
+ if (strcmp(procost, "1") != 0)
+ appendPQExpBuffer(q, " COST %s", procost);
+ }
+ else
+ {
+ /* default cost is 100 */
+ if (strcmp(procost, "100") != 0)
+ appendPQExpBuffer(q, " COST %s", procost);
+ }
+ }
+ if (proretset[0] == 't' &&
+ strcmp(prorows, "0") != 0 && strcmp(prorows, "1000") != 0)
+ appendPQExpBuffer(q, " ROWS %s", prorows);
+
+ for (i = 0; i < nconfigitems; i++)
+ {
+ /* we feel free to scribble on configitems[] here */
+ char *configitem = configitems[i];
+ char *pos;
+
+ pos = strchr(configitem, '=');
+ if (pos == NULL)
+ continue;
+ *pos++ = '\0';
+ appendPQExpBuffer(q, "\n SET %s TO ", fmtId(configitem));
+
+ /*
+ * Some GUC variable names are 'LIST' type and hence must not be
+ * quoted.
+ */
+ if (pg_strcasecmp(configitem, "DateStyle") == 0
+ || pg_strcasecmp(configitem, "search_path") == 0)
+ appendPQExpBuffer(q, "%s", pos);
+ else
+ appendStringLiteralConn(q, pos, conn);
+ }
+
+ appendPQExpBuffer(q, "\n%s;\n", asPart->data);
+
+ result = q->data;
+
+ out:
+ PQclear(res);
+ free(q);
+ destroyPQExpBuffer(query);
+ destroyPQExpBuffer(asPart);
+ free(funcsig);
+ if (allargtypes)
+ free(allargtypes);
+ if (argmodes)
+ free(argmodes);
+ if (argnames)
+ free(argnames);
+ if (configitems)
+ free(configitems);
+
+ return result;
+ }
*** /dev/null
--- b/src/bin/psql/dumpfunc.h
***************
*** 0 ****
--- 1,10 ----
+ #ifndef DUMPFUNC_H
+ #define DUMPFUNC_H
+
+ #include "postgres_fe.h"
+ #include "libpq-fe.h"
+
+ bool lookup_function_oid(PGconn *conn, const char *desc, Oid *result);
+ const char *create_or_replace_function_text(PGconn *conn, Oid oid);
+
+ #endif
Abhijit Menon-Sen <ams@oryx.com> writes:
Refactoring pg_dump was more work than I had time to do right now, and I
wanted \ef to work, so I hacked up the attached (by copying dumpFunc and
its dependencies to src/bin/psql/dumpfunc.[ch]).
I doubt we'd consider accepting a patch done this way.
regards, tom lane
At 2008-07-15 10:33:02 -0400, tgl@sss.pgh.pa.us wrote:
I doubt we'd consider accepting a patch done this way.
Yes, it's much too ugly to live. I was posting it only for the record,
I should have made that clear.
-- ams
At 2008-07-15 20:28:39 +0530, ams@oryx.com wrote:
I doubt we'd consider accepting a patch done this way.
Yes, it's much too ugly to live.
Though I must say it would have been even MORE horrible to copy all this
code into the backend to make pg_get_functiondef(), notwithstanding the
extra utility of a generally-callable function.
But what I'm wondering, since Gavin said he once had a working version
of this patch (i.e. \ef) which he somehow lost, is how he approached the
problem at the time.
Gavin? Do you remember? Was it horrible?
-- ams
Abhijit Menon-Sen <ams@oryx.com> writes:
Though I must say it would have been even MORE horrible to copy all this
code into the backend to make pg_get_functiondef(), notwithstanding the
extra utility of a generally-callable function.
FWIW, I just found myself forced to invent pg_get_function_arguments()
and pg_get_function_result(), because the TABLE function patch has
pushed the complexity of printing function argument and result types
well beyond the bounds of sanity. (Pavel had hacked up pg_dump and
ignored psql's \df ...) It wouldn't take a whole lot to convince me
that a pg_get_functiondef would be useful, although I don't foresee
either of those applications wanting to use it because of their
backward-compatibility constraints.
regards, tom lane
At 2008-07-17 18:28:19 -0400, tgl@sss.pgh.pa.us wrote:
It wouldn't take a whole lot to convince me that a pg_get_functiondef
would be useful, although I don't foresee either of those applications
wanting to use it because of their backward-compatibility constraints.
If the code lives in psql (as with my patch), then it has some chance of
working with older servers, but if you're happy with pg_get_functiondef,
then I'm happy enough to use it to get \ef working. I agree that pg_dump
wouldn't want to use it, of course, but I guess it doesn't matter very
much if \ef doesn't work on older servers.
What would the function return? "CREATE OR REPLACE FUNCTION ..."? Would
that be good enough for everyone who might want to call it?
(BTW, psql from 8.3 is already somewhat broken with 8.1:
archiveopteryx=> \d access_keys
ERROR: column i.indisvalid does not exist
And 8.2 as well:
archiveopteryx=> \d access_keys
ERROR: column t.tgconstraint does not exist
LINE 3: WHERE t.tgrelid = '16847' AND t.tgconstraint = 0
^
Oh, I see they've both been fixed in CVS. Sorry for the noise.)
-- ams
Abhijit Menon-Sen <ams@oryx.com> writes:
At 2008-07-17 18:28:19 -0400, tgl@sss.pgh.pa.us wrote:
It wouldn't take a whole lot to convince me that a pg_get_functiondef
would be useful, although I don't foresee either of those applications
wanting to use it because of their backward-compatibility constraints.
What would the function return? "CREATE OR REPLACE FUNCTION ..."? Would
that be good enough for everyone who might want to call it?
I think I'd go with CREATE FUNCTION for simplicity. It would be easy
enough for something like \ef to splice in OR REPLACE before shipping
the command back to the server.
regards, tom lane
On 7/23/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Abhijit Menon-Sen <ams@oryx.com> writes:
At 2008-07-17 18:28:19 -0400, tgl@sss.pgh.pa.us wrote:
It wouldn't take a whole lot to convince me that a pg_get_functiondef
would be useful, although I don't foresee either of those applications
wanting to use it because of their backward-compatibility constraints.What would the function return? "CREATE OR REPLACE FUNCTION ..."? Would
that be good enough for everyone who might want to call it?I think I'd go with CREATE FUNCTION for simplicity. It would be easy
enough for something like \ef to splice in OR REPLACE before shipping
the command back to the server.
Please make it use full qualified names (schema.name) for both
function name and result types. Current search_path juggling
the pg_dump does is major PITA.
--
marko
"Marko Kreen" <markokr@gmail.com> writes:
[ re pg_get_functiondef ]
Please make it use full qualified names (schema.name) for both
function name and result types. Current search_path juggling
the pg_dump does is major PITA.
Qualifying the function name seems like a good idea, but I'd advise
against tinkering with the datatype references. It'll be hard to
do correctly and it will make things very substantially uglier.
Do you really want to show, eg, "pg_catalog.int4" rather than "integer"?
If you leave the backend code do what it wants to do here, the only
way that there would be a problem is if someone changed their
search_path in between pg_get_functiondef and trying to re-load the
function definition. Which certainly ain't gonna happen for \ef,
and it seems a bit implausible for any other use-case either.
regards, tom lane
At 2008-07-23 10:50:03 -0400, tgl@sss.pgh.pa.us wrote:
What would the function return? "CREATE OR REPLACE FUNCTION ..."?
I think I'd go with CREATE FUNCTION for simplicity.
OK, I have a mostly working pg_get_functiondef now, and some questions
about the remaining pieces:
1. Why is "prorows" a float4? Should I print it that way, i.e. "%f"?
2. Can I print the contents of "proconfig" as just "SET %s"? It seems
to work for the variables I've tried (e.g. "DateStyle=iso"), but I
wonder if they'll always be quoted correctly (i.e., if the "split
on '='" thing pg_dump does is necessary for an 8.4 function).
3. Is there a function I can use from ruleutils.c to do dollar quoting
of prosrc/probin? If not, and I have to write one, where should it
live?
4. What exactly is probin? Do I need to worry about it at all?
Thanks.
-- ams
Marko is talking about types created with CREATE TYPE
CREATE FUNCTION fraud.get_user_status(
i_key_user text
) RETURNS ret_get_user_status AS
$$
Current pg_dump annoyingly removes schem reference from type.
On Wed, Jul 23, 2008 at 6:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"Marko Kreen" <markokr@gmail.com> writes:
[ re pg_get_functiondef ]
Please make it use full qualified names (schema.name) for both
function name and result types. Current search_path juggling
the pg_dump does is major PITA.Qualifying the function name seems like a good idea, but I'd advise
against tinkering with the datatype references. It'll be hard to
do correctly and it will make things very substantially uglier.
Do you really want to show, eg, "pg_catalog.int4" rather than "integer"?If you leave the backend code do what it wants to do here, the only
way that there would be a problem is if someone changed their
search_path in between pg_get_functiondef and trying to re-load the
function definition. Which certainly ain't gonna happen for \ef,
and it seems a bit implausible for any other use-case either.regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Not so sure about omitting OR REPLACE. In my experience it is more often
needed than not. Main argument for omitting might be to protect hackers from
carelesse users :)
On Wed, Jul 23, 2008 at 5:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Abhijit Menon-Sen <ams@oryx.com> writes:
At 2008-07-17 18:28:19 -0400, tgl@sss.pgh.pa.us wrote:
It wouldn't take a whole lot to convince me that a pg_get_functiondef
would be useful, although I don't foresee either of those applications
wanting to use it because of their backward-compatibility constraints.What would the function return? "CREATE OR REPLACE FUNCTION ..."? Would
that be good enough for everyone who might want to call it?I think I'd go with CREATE FUNCTION for simplicity. It would be easy
enough for something like \ef to splice in OR REPLACE before shipping
the command back to the server.regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jul 29, 2008 at 02:21:18PM -0400, Robert Haas wrote:
+1 for CREATE OR REPLACE
...Robert
+1 for CREATE OR REPLACE from me, too :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
At 2008-07-29 15:42:27 +0530, ams@oryx.com wrote:
OK, I have a mostly working pg_get_functiondef now, and some
questions about the remaining pieces:
While I look for answers to those questions, here's the patch as it
stands now, in case anyone feels like reading through it.
-- ams
Attachments:
functiondef.difftext/x-diff; charset=us-asciiDownload
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 1ba20b0..ccf0d68 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -551,6 +551,7 @@ extern Datum pg_get_expr(PG_FUNCTION_ARGS);
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0d28310..dbfeff5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -137,6 +137,7 @@ static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
bool print_table_args);
+static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
int prettyFlags);
static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -1398,6 +1399,129 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
/*
+ * pg_get_functiondef
+ * Returns the "CREATE FUNCTION ..." statement for a function.
+ */
+Datum
+pg_get_functiondef(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ StringInfoData buf;
+ HeapTuple proctup;
+ HeapTuple langtup;
+ Form_pg_proc proc;
+ Form_pg_language lang;
+ bool isnull;
+ Datum tmp;
+ const char *prosrc;
+ const char *name;
+ const char *nsp;
+ float4 cost;
+ int n;
+
+ initStringInfo(&buf);
+
+ proctup = SearchSysCache(PROCOID, ObjectIdGetDatum(funcid), 0, 0, 0);
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+
+ langtup = SearchSysCache(LANGOID, ObjectIdGetDatum(proc->prolang), 0, 0, 0);
+ if (!HeapTupleIsValid(langtup))
+ elog(ERROR, "cache lookup failed for language %u", proc->prolang);
+ lang = (Form_pg_language) GETSTRUCT(langtup);
+
+ name = NameStr(proc->proname);
+ nsp = get_namespace_name(proc->pronamespace);
+ appendStringInfo(&buf, "CREATE FUNCTION %s(",
+ quote_qualified_identifier(nsp, name));
+ (void) print_function_arguments(&buf, proctup, false);
+ appendStringInfoString(&buf, ")\n RETURNS ");
+ print_function_rettype(&buf, proctup);
+ appendStringInfo(&buf, "\n LANGUAGE '%s'\n", NameStr(lang->lanname));
+
+ n = 1;
+
+ switch (proc->provolatile) {
+ case PROVOLATILE_IMMUTABLE:
+ appendStringInfoString(&buf, " IMMUTABLE");
+ break;
+ case PROVOLATILE_STABLE:
+ appendStringInfoString(&buf, " STABLE");
+ break;
+ case PROVOLATILE_VOLATILE:
+ default:
+ n--;
+ break;
+ }
+
+ if (proc->proisstrict)
+ {
+ n++;
+ appendStringInfoString(&buf, " STRICT");
+ }
+
+ if (proc->prosecdef)
+ {
+ n++;
+ appendStringInfoString(&buf, " SECURITY DEFINER");
+ }
+
+ cost = 100;
+ if (proc->prolang == INTERNALlanguageId ||
+ proc->prolang == ClanguageId)
+ cost = 1;
+
+ if (proc->procost != cost)
+ {
+ n++;
+ appendStringInfo(&buf, " COST %f", proc->procost);
+ }
+
+ if (proc->prorows != 0 && proc->prorows != 1000)
+ {
+ n++;
+ appendStringInfo(&buf, " ROWS %f", proc->prorows);
+ }
+
+ if (n != 0)
+ appendStringInfoString(&buf, "\n");
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_proconfig, &isnull);
+ if (!isnull)
+ {
+ int i;
+ ArrayType *a = DatumGetArrayTypeP(tmp);
+
+ for (i = 1; i <= ARR_DIMS(a)[0]; i++)
+ {
+ Datum d;
+ bool isnull;
+
+ d = array_ref(a, 1, &i, -1, -1, false, 'i', &isnull);
+ if (!isnull)
+ {
+ const char *s = TextDatumGetCString(d);
+ appendStringInfo(&buf, " SET %s\n", s);
+ }
+ }
+ }
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosrc, &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ prosrc = TextDatumGetCString(tmp);
+
+ appendStringInfo(&buf, "AS $$\n%s\n$$;", prosrc);
+
+ ReleaseSysCache(langtup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/*
* pg_get_function_arguments
* Get a nicely-formatted list of arguments for a function.
* This is everything that would go between the parentheses in
@@ -1436,8 +1560,6 @@ pg_get_function_result(PG_FUNCTION_ARGS)
Oid funcid = PG_GETARG_OID(0);
StringInfoData buf;
HeapTuple proctup;
- Form_pg_proc procform;
- int ntabargs = 0;
initStringInfo(&buf);
@@ -1446,32 +1568,46 @@ pg_get_function_result(PG_FUNCTION_ARGS)
0, 0, 0);
if (!HeapTupleIsValid(proctup))
elog(ERROR, "cache lookup failed for function %u", funcid);
- procform = (Form_pg_proc) GETSTRUCT(proctup);
+ print_function_rettype(&buf, proctup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/* Appends a function's return type to the specified buffer. */
- if (procform->proretset)
+void print_function_rettype(StringInfo buf, HeapTuple proctup)
+{
+ int ntabargs = 0;
+ Form_pg_proc proc = (Form_pg_proc) GETSTRUCT(proctup);
+ StringInfoData b;
+
+ initStringInfo(&b);
+
+ if (proc->proretset)
{
/* It might be a table function; try to print the arguments */
- appendStringInfoString(&buf, "TABLE(");
- ntabargs = print_function_arguments(&buf, proctup, true);
+ appendStringInfoString(&b, "TABLE(");
+ ntabargs = print_function_arguments(&b, proctup, true);
if (ntabargs > 0)
- appendStringInfoString(&buf, ")");
+ appendStringInfoString(&b, ")");
else
- resetStringInfo(&buf);
+ resetStringInfo(&b);
}
if (ntabargs == 0)
{
/* Not a table function, so do the normal thing */
- if (procform->proretset)
- appendStringInfoString(&buf, "SETOF ");
- appendStringInfoString(&buf, format_type_be(procform->prorettype));
+ if (proc->proretset)
+ appendStringInfoString(&b, "SETOF ");
+ appendStringInfoString(&b, format_type_be(proc->prorettype));
}
- ReleaseSysCache(proctup);
-
- PG_RETURN_TEXT_P(string_to_text(buf.data));
+ appendStringInfoString(buf, b.data);
}
+
/*
* Common code for pg_get_function_arguments and pg_get_function_result:
* append the desired subset of arguments to buf. We print only TABLE
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 16ccb55..0f65534 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2291,6 +2291,8 @@ DATA(insert OID = 1716 ( pg_get_expr PGNSP PGUID 12 1 0 0 f f t f s 2 25 "2
DESCR("deparse an encoded expression");
DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 1 0 0 f f t f s 2 25 "25 25" _null_ _null_ _null_ pg_get_serial_sequence _null_ _null_ _null_ ));
DESCR("name of sequence for a serial column");
+DATA(insert OID = 2176 ( pg_get_functiondef PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_functiondef _null_ _null_ _null_ ));
+DESCR("definition of a function");
DATA(insert OID = 2162 ( pg_get_function_arguments PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_arguments _null_ _null_ _null_ ));
DESCR("argument list of a function");
DATA(insert OID = 2165 ( pg_get_function_result PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_result _null_ _null_ _null_ ));
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 448a302..b0b7438 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11563,6 +11563,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</indexterm>
<indexterm>
+ <primary>pg_get_functiondef</primary>
+ </indexterm>
+
+ <indexterm>
<primary>pg_get_function_arguments</primary>
</indexterm>
@@ -11644,6 +11648,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
in it refer to the relation indicated by the second parameter</entry>
</row>
<row>
+ <entry><literal><function>pg_get_functiondef</function>(<parameter>func_oid</parameter>)</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>get definition of a function</entry>
+ </row>
+ <row>
<entry><literal><function>pg_get_function_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
<entry><type>text</type></entry>
<entry>get argument list for function</entry>
@@ -11756,6 +11765,8 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</para>
<para>
+ <function>pg_get_functiondef</> returns the <command>CREATE FUNCTION</>
+ statement for a function.
<function>pg_get_function_arguments</function> returns the argument list
of a function, in the form it would need to appear in within
<command>CREATE FUNCTION</>.
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).
Comments appreciated.
-- ams
Attachments:
funcdef.difftext/x-diff; charset=us-asciiDownload
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 1ba20b0..ccf0d68 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -551,6 +551,7 @@ extern Datum pg_get_expr(PG_FUNCTION_ARGS);
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0d28310..71e601a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -137,6 +137,7 @@ static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
bool print_table_args);
+static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
int prettyFlags);
static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -1398,6 +1399,137 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
/*
+ * pg_get_functiondef
+ * Returns the "CREATE OR REPLACE FUNCTION ..." statement for the
+ * specified function.
+ */
+Datum
+pg_get_functiondef(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ StringInfoData buf;
+ StringInfoData dq;
+ HeapTuple proctup;
+ HeapTuple langtup;
+ Form_pg_proc proc;
+ Form_pg_language lang;
+ bool isnull;
+ Datum tmp;
+ const char *prosrc;
+ const char *name;
+ const char *nsp;
+ float4 cost;
+ int n;
+
+ initStringInfo(&buf);
+
+ proctup = SearchSysCache(PROCOID, ObjectIdGetDatum(funcid), 0, 0, 0);
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+
+ langtup = SearchSysCache(LANGOID, ObjectIdGetDatum(proc->prolang), 0, 0, 0);
+ if (!HeapTupleIsValid(langtup))
+ elog(ERROR, "cache lookup failed for language %u", proc->prolang);
+ lang = (Form_pg_language) GETSTRUCT(langtup);
+
+ name = NameStr(proc->proname);
+ nsp = get_namespace_name(proc->pronamespace);
+ appendStringInfo(&buf, "CREATE OR REPLACE FUNCTION %s(",
+ quote_qualified_identifier(nsp, name));
+ (void) print_function_arguments(&buf, proctup, false);
+ appendStringInfoString(&buf, ")\n RETURNS ");
+ print_function_rettype(&buf, proctup);
+ appendStringInfo(&buf, "\n LANGUAGE '%s'\n", NameStr(lang->lanname));
+
+ n = 1;
+
+ switch (proc->provolatile) {
+ case PROVOLATILE_IMMUTABLE:
+ appendStringInfoString(&buf, " IMMUTABLE");
+ break;
+ case PROVOLATILE_STABLE:
+ appendStringInfoString(&buf, " STABLE");
+ break;
+ case PROVOLATILE_VOLATILE:
+ default:
+ n--;
+ break;
+ }
+
+ if (proc->proisstrict)
+ {
+ n++;
+ appendStringInfoString(&buf, " STRICT");
+ }
+
+ if (proc->prosecdef)
+ {
+ n++;
+ appendStringInfoString(&buf, " SECURITY DEFINER");
+ }
+
+ cost = 100;
+ if (proc->prolang == INTERNALlanguageId ||
+ proc->prolang == ClanguageId)
+ cost = 1;
+
+ if (proc->procost != cost)
+ {
+ n++;
+ appendStringInfo(&buf, " COST %.0f", proc->procost);
+ }
+
+ if (proc->prorows != 0 && proc->prorows != 1000)
+ {
+ n++;
+ appendStringInfo(&buf, " ROWS %.0f", proc->prorows);
+ }
+
+ if (n != 0)
+ appendStringInfoString(&buf, "\n");
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_proconfig, &isnull);
+ if (!isnull)
+ {
+ int i;
+ ArrayType *a = DatumGetArrayTypeP(tmp);
+
+ for (i = 1; i <= ARR_DIMS(a)[0]; i++)
+ {
+ Datum d;
+ bool isnull;
+
+ d = array_ref(a, 1, &i, -1, -1, false, 'i', &isnull);
+ if (!isnull)
+ {
+ const char *s = TextDatumGetCString(d);
+ appendStringInfo(&buf, " SET %s\n", s);
+ }
+ }
+ }
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosrc, &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ prosrc = TextDatumGetCString(tmp);
+
+ initStringInfo(&dq);
+ appendStringInfoString(&dq, "$");
+ while (strstr(prosrc, dq.data) != NULL)
+ appendStringInfoString(&dq, "x");
+ appendStringInfoString(&dq, "$");
+
+ appendStringInfo(&buf, "AS %s\n%s\n%s;", dq.data, prosrc, dq.data);
+
+ ReleaseSysCache(langtup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/*
* pg_get_function_arguments
* Get a nicely-formatted list of arguments for a function.
* This is everything that would go between the parentheses in
@@ -1436,8 +1568,6 @@ pg_get_function_result(PG_FUNCTION_ARGS)
Oid funcid = PG_GETARG_OID(0);
StringInfoData buf;
HeapTuple proctup;
- Form_pg_proc procform;
- int ntabargs = 0;
initStringInfo(&buf);
@@ -1446,32 +1576,46 @@ pg_get_function_result(PG_FUNCTION_ARGS)
0, 0, 0);
if (!HeapTupleIsValid(proctup))
elog(ERROR, "cache lookup failed for function %u", funcid);
- procform = (Form_pg_proc) GETSTRUCT(proctup);
+ print_function_rettype(&buf, proctup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/* Appends a function's return type to the specified buffer. */
- if (procform->proretset)
+void print_function_rettype(StringInfo buf, HeapTuple proctup)
+{
+ int ntabargs = 0;
+ Form_pg_proc proc = (Form_pg_proc) GETSTRUCT(proctup);
+ StringInfoData b;
+
+ initStringInfo(&b);
+
+ if (proc->proretset)
{
/* It might be a table function; try to print the arguments */
- appendStringInfoString(&buf, "TABLE(");
- ntabargs = print_function_arguments(&buf, proctup, true);
+ appendStringInfoString(&b, "TABLE(");
+ ntabargs = print_function_arguments(&b, proctup, true);
if (ntabargs > 0)
- appendStringInfoString(&buf, ")");
+ appendStringInfoString(&b, ")");
else
- resetStringInfo(&buf);
+ resetStringInfo(&b);
}
if (ntabargs == 0)
{
/* Not a table function, so do the normal thing */
- if (procform->proretset)
- appendStringInfoString(&buf, "SETOF ");
- appendStringInfoString(&buf, format_type_be(procform->prorettype));
+ if (proc->proretset)
+ appendStringInfoString(&b, "SETOF ");
+ appendStringInfoString(&b, format_type_be(proc->prorettype));
}
- ReleaseSysCache(proctup);
-
- PG_RETURN_TEXT_P(string_to_text(buf.data));
+ appendStringInfoString(buf, b.data);
}
+
/*
* Common code for pg_get_function_arguments and pg_get_function_result:
* append the desired subset of arguments to buf. We print only TABLE
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 16ccb55..0f65534 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2291,6 +2291,8 @@ DATA(insert OID = 1716 ( pg_get_expr PGNSP PGUID 12 1 0 0 f f t f s 2 25 "2
DESCR("deparse an encoded expression");
DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 1 0 0 f f t f s 2 25 "25 25" _null_ _null_ _null_ pg_get_serial_sequence _null_ _null_ _null_ ));
DESCR("name of sequence for a serial column");
+DATA(insert OID = 2176 ( pg_get_functiondef PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_functiondef _null_ _null_ _null_ ));
+DESCR("definition of a function");
DATA(insert OID = 2162 ( pg_get_function_arguments PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_arguments _null_ _null_ _null_ ));
DESCR("argument list of a function");
DATA(insert OID = 2165 ( pg_get_function_result PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_result _null_ _null_ _null_ ));
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 448a302..b0b7438 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11563,6 +11563,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</indexterm>
<indexterm>
+ <primary>pg_get_functiondef</primary>
+ </indexterm>
+
+ <indexterm>
<primary>pg_get_function_arguments</primary>
</indexterm>
@@ -11644,6 +11648,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
in it refer to the relation indicated by the second parameter</entry>
</row>
<row>
+ <entry><literal><function>pg_get_functiondef</function>(<parameter>func_oid</parameter>)</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>get definition of a function</entry>
+ </row>
+ <row>
<entry><literal><function>pg_get_function_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
<entry><type>text</type></entry>
<entry>get argument list for function</entry>
@@ -11756,6 +11765,8 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</para>
<para>
+ <function>pg_get_functiondef</> returns the <command>CREATE FUNCTION</>
+ statement for a function.
<function>pg_get_function_arguments</function> returns the argument list
of a function, in the form it would need to appear in within
<command>CREATE FUNCTION</>.
edit.difftext/x-diff; charset=us-asciiDownload
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9e6923f..fd61034 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -56,9 +56,12 @@
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
PQExpBuffer query_buf);
-static bool do_edit(const char *filename_arg, PQExpBuffer query_buf);
+static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
+ bool *edited);
static bool do_connect(char *dbname, char *user, char *host, char *port);
static bool do_shell(const char *command);
+static bool lookup_function_oid(PGconn *conn, const char *desc, Oid *result);
+static const char *create_or_replace_function_text(PGconn *conn, Oid oid);
#ifdef USE_SSL
static void printSSLInfo(void);
@@ -444,11 +447,76 @@ exec_command(const char *cmd,
expand_tilde(&fname);
if (fname)
canonicalize_path(fname);
- status = do_edit(fname, query_buf) ? PSQL_CMD_NEWEDIT : PSQL_CMD_ERROR;
+ if (do_edit(fname, query_buf, NULL))
+ status = PSQL_CMD_NEWEDIT;
+ else
+ status = PSQL_CMD_ERROR;
free(fname);
}
}
+ /*
+ * \ef -- edit the named function in $EDITOR.
+ */
+
+ else if (strcmp(cmd, "ef") == 0)
+ {
+ Oid foid;
+ char *func;
+
+ func = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
+ if (!func)
+ {
+ psql_error("no function name specified\n");
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!lookup_function_oid(pset.db, func, &foid))
+ {
+ psql_error(PQerrorMessage(pset.db));
+ status = PSQL_CMD_ERROR;
+ }
+ else {
+ termPQExpBuffer(query_buf);
+ if (foid)
+ {
+ char *s = create_or_replace_function_text(pset.db, foid);
+ if (s)
+ {
+ appendPQExpBufferStr(query_buf, s);
+ free(s);
+ }
+ else
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ {
+ printfPQExpBuffer(query_buf,
+ "CREATE FUNCTION %s%s RETURNS ... AS $$\n"
+ "...\n"
+ "$$ LANGUAGE '...'\n",
+ func, strchr(func,'(') ? "" : "(...)" );
+ }
+ }
+
+ if (status != PSQL_CMD_ERROR)
+ {
+ bool edited = false;
+ if (!do_edit(0, query_buf, &edited))
+ {
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!edited)
+ {
+ printf("No changes\n");
+ }
+ else
+ {
+ status = PSQL_CMD_SEND;
+ }
+ free(func);
+ }
+ }
+
/* \echo and \qecho */
else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0)
{
@@ -1410,7 +1478,7 @@ editFile(const char *fname)
/* call this one */
static bool
-do_edit(const char *filename_arg, PQExpBuffer query_buf)
+do_edit(const char *filename_arg, PQExpBuffer query_buf, bool *edited)
{
char fnametmp[MAXPGPATH];
FILE *stream = NULL;
@@ -1532,6 +1600,10 @@ do_edit(const char *filename_arg, PQExpBuffer query_buf)
psql_error("%s: %s\n", fname, strerror(errno));
error = true;
}
+ else if (edited)
+ {
+ *edited = true;
+ }
fclose(stream);
}
@@ -1912,3 +1984,66 @@ do_shell(const char *command)
}
return true;
}
+
+/*
+ * This function takes a function description, e.g. "x" or "x(int)", and
+ * issues a query on the given connection to retrieve the function's oid
+ * using a cast to regproc or regprocedure (as appropriate). The result,
+ * if there is one, is stored in the integer pointed to by result, which
+ * is assumed to be non-zero. If there are no results (i.e. the function
+ * does not exist), 0 is stored. The function then returns true.
+ *
+ * If the oid lookup query fails (which it will, for example, when
+ * multiple functions match the given description), it returns false.
+ */
+
+static bool
+lookup_function_oid(PGconn *conn, const char *desc, Oid *result)
+{
+ PGresult *res;
+ PQExpBuffer buf;
+
+ buf = createPQExpBuffer();
+ printfPQExpBuffer(buf, "SELECT '%s'::%s::oid",
+ desc, strchr(desc, '(') ? "regprocedure" : "regproc");
+
+ res = PQexec(conn, buf->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+ return false;
+
+ *result = 0;
+ if (PQntuples(res) > 0)
+ *result = atooid(PQgetvalue(res, 0, 0));
+
+ destroyPQExpBuffer(buf);
+ PQclear(res);
+
+ return true;
+}
+
+/*
+ * Returns the "CREATE OR REPLACE FUNCTION ..." statement that was used
+ * to create the function with the given oid, which is assumed to be the
+ * result of lookup_function_oid() (i.e. a valid oid from pg_proc).
+ */
+
+static const char *
+create_or_replace_function_text(PGconn *conn, Oid oid)
+{
+ PGresult *res;
+ PQExpBuffer buf;
+ const char *s = 0;
+
+ buf = createPQExpBuffer();
+ printfPQExpBuffer(buf, "SELECT pg_get_functiondef(%d)", oid);
+
+ res = PQexec(conn, buf->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1)
+ return NULL;
+ s = pg_strdup(PQgetvalue(res, 0, 0));
+
+ destroyPQExpBuffer(buf);
+ PQclear(res);
+
+ return s;
+}
On Jul 31, 2008, at 00:07, Abhijit Menon-Sen wrote:
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).Comments appreciated.
+1
I like! The ability to easily edit a function on the fly in psql will
be very welcome to DBAs I know. And I like the pg_get_functiondef()
function, too, a that will simplify editing existing functions in
other admin apps, like pgAdmin.
I'm starting to get really excited for 8.4. I can haz cheezburger?
Oops, I mean, when does it ship? ;-P
Thanks,
David
On Mon, Aug 04, 2008 at 10:31:10AM -0700, David Wheeler wrote:
On Jul 31, 2008, at 00:07, Abhijit Menon-Sen wrote:
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).Comments appreciated.
+1
I like! The ability to easily edit a function on the fly in psql
will be very welcome to DBAs I know. And I like the
pg_get_functiondef() function, too, a that will simplify editing
existing functions in other admin apps, like pgAdmin.I'm starting to get really excited for 8.4. I can haz cheezburger?
You do understand you've just kicked off a discussion of shipping
PL/LOLCODE by default.
Oops, I mean, when does it ship? ;-P
Christmas ;)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Abhijit Menon-Sen <ams@oryx.com> writes:
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).
I've applied this with some corrections (mostly around proper quoting)
and some outright editorialization:
* the psql command seemed to have some ideas about supplying a blank
CREATE OR REPLACE FUNCTION command for a nonexistent function, but this
didn't actually work. In any case it seemed poorly thought out, because
you'd really need to pay some attention to *why* the regproc/regprocedure
lookup failed. I just ripped it out for the moment. I'm not averse to
the concept, if you can get it implemented properly.
* the way you had it set up, the CREATE OR REPLACE FUNCTION command
would be sent to the backend instantaneously upon return from the
editor, with no opportunity for the user to decide he didn't want his
changes applied. This seemed unacceptably dangerous to me. I changed
the exit code to PSQL_CMD_NEWEDIT instead of PSQL_CMD_SEND, which causes
the command to wait in the query buffer. Unfortunately there's no
visual indication of that, other than a small change in the prompt
status. It'd likely be better if we could get libreadline to redisplay
the query buffer contents --- anyone have an idea how to do that?
(I have some vague recollection that \e used to work that way, though
it definitely fails to do so now.)
regards, tom lane
On Sat, Sep 6, 2008 at 10:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
* the way you had it set up, the CREATE OR REPLACE FUNCTION command
would be sent to the backend instantaneously upon return from the
editor, with no opportunity for the user to decide he didn't want his
changes applied. This seemed unacceptably dangerous to me. I changed
the exit code to PSQL_CMD_NEWEDIT instead of PSQL_CMD_SEND, which causes
the command to wait in the query buffer.
The principle of least astonishment suggests that \ef should behave in
the same way as \e.
With \e (which I use a lot), the command(s) are immediately executed
by the backend as soon as you write and exit from the editor. I don't
find that dangerous, and anyone who uses \e will already be very much
accustomed to it. If \ef did something different, it would just be
weird.
If you're not sure you want to execute the contents of your \e editor
session after all, you can always delete the semicolon, or everything
in the file, before quitting.
Cheers,
BJ
"Brendan Jurd" <direvus@gmail.com> writes:
On Sat, Sep 6, 2008 at 10:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
... I changed
the exit code to PSQL_CMD_NEWEDIT instead of PSQL_CMD_SEND, which causes
the command to wait in the query buffer.
The principle of least astonishment suggests that \ef should behave in
the same way as \e.
Quite.
regards, tom lane
I wrote:
* the psql command seemed to have some ideas about supplying a blank
CREATE OR REPLACE FUNCTION command for a nonexistent function, but this
didn't actually work. In any case it seemed poorly thought out, because
you'd really need to pay some attention to *why* the regproc/regprocedure
lookup failed. I just ripped it out for the moment. I'm not averse to
the concept, if you can get it implemented properly.
While I was out at dinner, the obvious solution presented itself: define
\ef with no argument as being the command that presents an empty CREATE
FUNCTION command template to fill in. This isn't any more or less
typing than where I think you were going, and it eliminates all the
ambiguity about whether you meant to type a nonexistent function name
or just mistyped.
regards, tom lane
I wrote:
... define
\ef with no argument as being the command that presents an empty CREATE
FUNCTION command template to fill in.
No complaints? I'll go make that happen.
What about the general issue that neither \e nor \ef leave you with a
presentation of what's in the query buffer? I haven't studied readline
enough to know if that's fixable.
regards, tom lane
At 2008-09-06 14:58:25 -0400, tgl@sss.pgh.pa.us wrote:
I wrote:
... define
\ef with no argument as being the command that presents an empty
CREATE FUNCTION command template to fill in.No complaints? I'll go make that happen.
No complaints, it sounds fine to me.
What about the general issue that neither \e nor \ef leave you with a
presentation of what's in the query buffer?
I don't know how that can be fixed; but I agree with Brendan that it's
behaviour that people are used to, and that it can be left alone for
now.
-- ams
Tom Lane wrote:
"Brendan Jurd" <direvus@gmail.com> writes:
On Sat, Sep 6, 2008 at 10:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
... I changed
the exit code to PSQL_CMD_NEWEDIT instead of PSQL_CMD_SEND, which causes
the command to wait in the query buffer.The principle of least astonishment suggests that \ef should behave in
the same way as \e.Quite.
So, are they consistent now or do we need another patch?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
"Brendan Jurd" <direvus@gmail.com> writes:
The principle of least astonishment suggests that \ef should behave in
the same way as \e.Quite.
So, are they consistent now or do we need another patch?
They are consistent, I just don't like either of them ;-)
regards, tom lane
Abhijit Menon-Sen wrote:
At 2008-09-06 14:58:25 -0400, tgl@sss.pgh.pa.us wrote:
What about the general issue that neither \e nor \ef leave you with a
presentation of what's in the query buffer?I don't know how that can be fixed; but I agree with Brendan that it's
behaviour that people are used to, and that it can be left alone for
now.
As far as it works to not execute the query when the user exits without
saving the buffer, it should be OK.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Abhijit Menon-Sen wrote:
At 2008-09-06 14:58:25 -0400, tgl@sss.pgh.pa.us wrote:
What about the general issue that neither \e nor \ef leave you with a
presentation of what's in the query buffer?I don't know how that can be fixed; but I agree with Brendan that it's
behaviour that people are used to, and that it can be left alone for
now.As far as it works to not execute the query when the user exits without
saving the buffer, it should be OK.
Well, it works like \e now, which is good. The only complexity is that
\e works differently depending on whether you use ';' or \g, meaning:
SELECT 1;
\e
will execute the buffer on exit (saved or not), while
SELECT 1
\g
\e
will not execute the buffer on editor exit. Our current \ef code does
not add a trailing semicolon to the CREATE FUNCTION buffer contents so
it works like the second case, which is probably the best we are going
to do.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +