I have attached two patches: - funcdef.diff implements pg_get_functiondef() - edit.diff implements "\ef function" in psql based on (1).
Comments appreciated. -- ams
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; +}
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers