Re: [HACKERS] [PATCH] "\ef " in psql

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</>.
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;
+}
I have attached two patches:

- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).

Comments appreciated.

-- ams