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..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</>.
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