From 2c863ca818ba0a9704dbfe24eb578870b54bfee8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 6 Sep 2008 00:01:25 +0000 Subject: [PATCH] Implement a psql command "\ef" to edit the definition of a function. In support of that, create a backend function pg_get_functiondef(). The psql command is functional but maybe a bit rough around the edges... Abhijit Menon-Sen --- doc/src/sgml/func.sgml | 13 +- doc/src/sgml/ref/psql-ref.sgml | 25 ++- src/backend/utils/adt/ruleutils.c | 285 +++++++++++++++++++++++++----- src/bin/psql/command.c | 132 +++++++++++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 4 +- src/include/utils/builtins.h | 3 +- 7 files changed, 407 insertions(+), 59 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 15162ed5ed..7d8608d01e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -11562,6 +11562,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_get_ruledef + + pg_get_functiondef + + pg_get_function_arguments @@ -11643,6 +11647,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); decompile internal form of an expression, assuming that any Vars in it refer to the relation indicated by the second parameter + + pg_get_functiondef(func_oid) + text + get definition of a function + pg_get_function_arguments(func_oid) text @@ -11756,6 +11765,8 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); + pg_get_functiondef returns a complete + CREATE OR REPLACE FUNCTION statement for a function. pg_get_function_arguments returns the argument list of a function, in the form it would need to appear in within CREATE FUNCTION. diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index eca3038574..2eedbb54b4 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -1195,6 +1195,29 @@ testdb=> + + \ef function_description + + + + This command fetches and edits the definition of the named function, + in the form of a CREATE OR REPLACE FUNCTION command. + Editing is done in the same way as for \e. + After the editor exits, the updated command waits in the query buffer; + type semicolon or \g to send it, or \r + to cancel. + + + + The target function can be specified by name alone, or by name + and arguments, for example foo(integer, text). + The argument types must be given if there is more + than one function of the same name. + + + + + \echo text [ ... ] diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 66bbaaaa68..07c39fefda 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.282 2008/09/01 20:42:45 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.283 2008/09/06 00:01:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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, @@ -180,6 +181,7 @@ static void get_coercion_expr(Node *arg, deparse_context *context, Node *parentNode); static void get_const_expr(Const *constval, deparse_context *context, int showtype); +static void simple_quote_literal(StringInfo buf, const char *val); static void get_sublink_expr(SubLink *sublink, deparse_context *context); static void get_from_clause(Query *query, const char *prefix, deparse_context *context); @@ -553,23 +555,10 @@ pg_get_triggerdef(PG_FUNCTION_ARGS) { if (i > 0) appendStringInfo(&buf, ", "); - - /* - * We form the string literal according to the prevailing setting - * of standard_conforming_strings; we never use E''. User is - * responsible for making sure result is used correctly. - */ - appendStringInfoChar(&buf, '\''); - while (*p) - { - char ch = *p++; - - if (SQL_STR_DOUBLE(ch, !standard_conforming_strings)) - appendStringInfoChar(&buf, ch); - appendStringInfoChar(&buf, ch); - } - appendStringInfoChar(&buf, '\''); + simple_quote_literal(&buf, p); /* advance p to next string embedded in tgargs */ + while (*p) + p++; p++; } } @@ -1397,6 +1386,182 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS) } +/* + * pg_get_functiondef + * Returns the complete "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; + Datum tmp; + bool isnull; + const char *prosrc; + const char *name; + const char *nsp; + float4 procost; + int oldlen; + + initStringInfo(&buf); + + /* Look up the function */ + 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); + name = NameStr(proc->proname); + + if (proc->proisagg) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is an aggregate function", name))); + + /* Need its pg_language tuple for the language name */ + 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); + + /* + * We always qualify the function name, to ensure the right function + * gets replaced. + */ + 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", + quote_identifier(NameStr(lang->lanname))); + + /* Emit some miscellaneous options on one line */ + oldlen = buf.len; + + switch (proc->provolatile) + { + case PROVOLATILE_IMMUTABLE: + appendStringInfoString(&buf, " IMMUTABLE"); + break; + case PROVOLATILE_STABLE: + appendStringInfoString(&buf, " STABLE"); + break; + case PROVOLATILE_VOLATILE: + break; + } + if (proc->proisstrict) + appendStringInfoString(&buf, " STRICT"); + if (proc->prosecdef) + appendStringInfoString(&buf, " SECURITY DEFINER"); + + /* This code for the default cost and rows should match functioncmds.c */ + if (proc->prolang == INTERNALlanguageId || + proc->prolang == ClanguageId) + procost = 1; + else + procost = 100; + if (proc->procost != procost) + appendStringInfo(&buf, " COST %g", proc->procost); + + if (proc->prorows > 0 && proc->prorows != 1000) + appendStringInfo(&buf, " ROWS %g", proc->prorows); + + if (oldlen != buf.len) + appendStringInfoChar(&buf, '\n'); + + /* Emit any proconfig options, one per line */ + tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_proconfig, &isnull); + if (!isnull) + { + ArrayType *a = DatumGetArrayTypeP(tmp); + int i; + + Assert(ARR_ELEMTYPE(a) == TEXTOID); + Assert(ARR_NDIM(a) == 1); + Assert(ARR_LBOUND(a)[0] == 1); + + for (i = 1; i <= ARR_DIMS(a)[0]; i++) + { + Datum d; + + d = array_ref(a, 1, &i, + -1 /* varlenarray */ , + -1 /* TEXT's typlen */ , + false /* TEXT's typbyval */ , + 'i' /* TEXT's typalign */ , + &isnull); + if (!isnull) + { + char *configitem = TextDatumGetCString(d); + char *pos; + + pos = strchr(configitem, '='); + if (pos == NULL) + continue; + *pos++ = '\0'; + + appendStringInfo(&buf, " SET %s TO ", + quote_identifier(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) + appendStringInfoString(&buf, pos); + else + simple_quote_literal(&buf, pos); + appendStringInfoChar(&buf, '\n'); + } + } + } + + /* And finally the function definition ... */ + appendStringInfoString(&buf, "AS "); + + tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_probin, &isnull); + if (!isnull) + { + simple_quote_literal(&buf, TextDatumGetCString(tmp)); + appendStringInfoString(&buf, ", "); /* assume prosrc isn't null */ + } + + tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosrc, &isnull); + if (isnull) + elog(ERROR, "null prosrc"); + prosrc = TextDatumGetCString(tmp); + + /* We always use dollar quoting. Figure out a suitable delimiter. */ + initStringInfo(&dq); + appendStringInfoChar(&dq, '$'); + while (strstr(prosrc, dq.data) != NULL) + appendStringInfoChar(&dq, 'x'); + appendStringInfoChar(&dq, '$'); + + appendStringInfoString(&buf, dq.data); + appendStringInfoString(&buf, prosrc); + appendStringInfoString(&buf, dq.data); + + appendStringInfoString(&buf, "\n"); + + 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. @@ -1436,8 +1601,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,30 +1609,47 @@ 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); - if (procform->proretset) + print_function_rettype(&buf, proctup); + + ReleaseSysCache(proctup); + + PG_RETURN_TEXT_P(string_to_text(buf.data)); +} + +/* + * Guts of pg_get_function_result: append the function's return type + * to the specified buffer. + */ +static void +print_function_rettype(StringInfo buf, HeapTuple proctup) +{ + Form_pg_proc proc = (Form_pg_proc) GETSTRUCT(proctup); + int ntabargs = 0; + StringInfoData rbuf; + + initStringInfo(&rbuf); + + 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(&rbuf, "TABLE("); + ntabargs = print_function_arguments(&rbuf, proctup, true); if (ntabargs > 0) - appendStringInfoString(&buf, ")"); + appendStringInfoString(&rbuf, ")"); else - resetStringInfo(&buf); + resetStringInfo(&rbuf); } 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(&rbuf, "SETOF "); + appendStringInfoString(&rbuf, format_type_be(proc->prorettype)); } - ReleaseSysCache(proctup); - - PG_RETURN_TEXT_P(string_to_text(buf.data)); + appendStringInfoString(buf, rbuf.data); } /* @@ -4597,7 +4777,6 @@ get_const_expr(Const *constval, deparse_context *context, int showtype) Oid typoutput; bool typIsVarlena; char *extval; - char *valptr; bool isfloat = false; bool needlabel; @@ -4672,22 +4851,7 @@ get_const_expr(Const *constval, deparse_context *context, int showtype) break; default: - - /* - * We form the string literal according to the prevailing setting - * of standard_conforming_strings; we never use E''. User is - * responsible for making sure result is used correctly. - */ - appendStringInfoChar(buf, '\''); - for (valptr = extval; *valptr; valptr++) - { - char ch = *valptr; - - if (SQL_STR_DOUBLE(ch, !standard_conforming_strings)) - appendStringInfoChar(buf, ch); - appendStringInfoChar(buf, ch); - } - appendStringInfoChar(buf, '\''); + simple_quote_literal(buf, extval); break; } @@ -4729,6 +4893,31 @@ get_const_expr(Const *constval, deparse_context *context, int showtype) constval->consttypmod)); } +/* + * simple_quote_literal - Format a string as a SQL literal, append to buf + */ +static void +simple_quote_literal(StringInfo buf, const char *val) +{ + const char *valptr; + + /* + * We form the string literal according to the prevailing setting + * of standard_conforming_strings; we never use E''. User is + * responsible for making sure result is used correctly. + */ + appendStringInfoChar(buf, '\''); + for (valptr = val; *valptr; valptr++) + { + char ch = *valptr; + + if (SQL_STR_DOUBLE(ch, !standard_conforming_strings)) + appendStringInfoChar(buf, ch); + appendStringInfoChar(buf, ch); + } + appendStringInfoChar(buf, '\''); +} + /* ---------- * get_sublink_expr - Parse back a sublink diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 958e134a30..e1887a2472 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -3,7 +3,7 @@ * * Copyright (c) 2000-2008, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.193 2008/08/16 00:16:56 momjian Exp $ + * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.194 2008/09/06 00:01:24 tgl Exp $ */ #include "postgres_fe.h" #include "command.h" @@ -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 *foid); +static bool get_create_function_cmd(PGconn *conn, Oid oid, PQExpBuffer buf); #ifdef USE_SSL static void printSSLInfo(void); @@ -444,11 +447,64 @@ 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) + { + char *func; + Oid foid; + + 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 if (!query_buf) + { + psql_error("no query buffer\n"); + status = PSQL_CMD_ERROR; + } + else if (!get_create_function_cmd(pset.db, foid, query_buf)) + { + psql_error(PQerrorMessage(pset.db)); + status = PSQL_CMD_ERROR; + } + else + { + 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_NEWEDIT; + } + } + if (func) + free(func); + } + /* \echo and \qecho */ else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0) { @@ -1410,7 +1466,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,10 +1588,13 @@ 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); } - } /* remove temp file */ @@ -1912,3 +1971,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 returned at *foid. Note that we'll fail if the + * function doesn't exist OR if there are multiple matching candidates + * OR if there's something syntactically wrong with the function description; + * unfortunately it can be hard to tell the difference. + */ +static bool +lookup_function_oid(PGconn *conn, const char *desc, Oid *foid) +{ + bool result = true; + PQExpBuffer query; + PGresult *res; + + query = createPQExpBuffer(); + printfPQExpBuffer(query, "SELECT "); + appendStringLiteralConn(query, desc, conn); + appendPQExpBuffer(query, "::pg_catalog.%s::pg_catalog.oid", + strchr(desc, '(') ? "regprocedure" : "regproc"); + + res = PQexec(conn, query->data); + if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1) + *foid = atooid(PQgetvalue(res, 0, 0)); + else + result = false; + + PQclear(res); + destroyPQExpBuffer(query); + + return result; +} + +/* + * Fetches the "CREATE OR REPLACE FUNCTION ..." command that describes the + * function with the given OID. If successful, the result is stored in buf. + */ +static bool +get_create_function_cmd(PGconn *conn, Oid oid, PQExpBuffer buf) +{ + bool result = true; + PQExpBuffer query; + PGresult *res; + + query = createPQExpBuffer(); + printfPQExpBuffer(query, "SELECT pg_catalog.pg_get_functiondef(%u)", oid); + + res = PQexec(conn, query->data); + if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1) + { + resetPQExpBuffer(buf); + appendPQExpBufferStr(buf, PQgetvalue(res, 0, 0)); + } + else + result = false; + + PQclear(res); + destroyPQExpBuffer(query); + + return result; +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index ecf1004838..fd596bd52b 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.482 2008/09/01 20:42:45 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.483 2008/09/06 00:01:24 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200808311 +#define CATALOG_VERSION_NO 200809051 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 721ba9d0c0..cf946508ae 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.512 2008/08/25 11:18:43 mha Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.513 2008/09/06 00:01:24 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2292,6 +2292,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 = 2098 ( 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/src/include/utils/builtins.h b/src/include/utils/builtins.h index fbe5a119cb..09d38e9c1c 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.319 2008/07/18 03:32:53 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.320 2008/09/06 00:01:25 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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, -- 2.40.0