From 7a32ac8a66903de8c352735f2a26f610f5e47090 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 13 Feb 2018 10:34:04 -0500 Subject: [PATCH] Add procedure support to pg_get_functiondef This also makes procedures work in psql's \ef and \sf commands. Reported-by: Pavel Stehule --- doc/src/sgml/func.sgml | 8 +++---- doc/src/sgml/ref/psql-ref.sgml | 10 +++++---- src/backend/utils/adt/ruleutils.c | 22 ++++++++++++++----- .../regress/expected/create_procedure.out | 11 ++++++++++ src/test/regress/sql/create_procedure.sql | 1 + 5 files changed, 38 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 640ff09a7b9..4be31b082a8 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17008,22 +17008,22 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_get_functiondef(func_oid) text - get definition of a function + get definition of a function or procedure pg_get_function_arguments(func_oid) text - get argument list of function's definition (with default values) + get argument list of function's or procedure's definition (with default values) pg_get_function_identity_arguments(func_oid) text - get argument list to identify a function (without default values) + get argument list to identify a function or procedure (without default values) pg_get_function_result(func_oid) text - get RETURNS clause for function + get RETURNS clause for function (returns null for a procedure) pg_get_indexdef(index_oid) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 6f9b30b673c..8bd9b9387ec 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1815,8 +1815,9 @@ Tue Oct 26 21:40:57 CEST 1999 - This command fetches and edits the definition of the named function, - in the form of a CREATE OR REPLACE FUNCTION command. + This command fetches and edits the definition of the named function or procedure, + in the form of a CREATE OR REPLACE FUNCTION or + CREATE OR REPLACE PROCEDURE command. Editing is done in the same way as for \edit. After the editor exits, the updated command waits in the query buffer; type semicolon or \g to send it, or \r @@ -2970,8 +2971,9 @@ testdb=> \setenv LESS -imx4F - This command fetches and shows the definition of the named function, - in the form of a CREATE OR REPLACE FUNCTION command. + This command fetches and shows the definition of the named function or procedure, + in the form of a CREATE OR REPLACE FUNCTION or + CREATE OR REPLACE PROCEDURE command. The definition is printed to the current query output channel, as set by \o. diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 3bb468bdada..ba9fab4582f 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2449,6 +2449,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS) StringInfoData dq; HeapTuple proctup; Form_pg_proc proc; + bool isfunction; Datum tmp; bool isnull; const char *prosrc; @@ -2472,20 +2473,28 @@ pg_get_functiondef(PG_FUNCTION_ARGS) (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is an aggregate function", name))); + isfunction = (proc->prorettype != InvalidOid); + /* * 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(", + appendStringInfo(&buf, "CREATE OR REPLACE %s %s(", + isfunction ? "FUNCTION" : "PROCEDURE", quote_qualified_identifier(nsp, name)); (void) print_function_arguments(&buf, proctup, false, true); - appendStringInfoString(&buf, ")\n RETURNS "); - print_function_rettype(&buf, proctup); + appendStringInfoString(&buf, ")\n"); + if (isfunction) + { + appendStringInfoString(&buf, " RETURNS "); + print_function_rettype(&buf, proctup); + appendStringInfoChar(&buf, '\n'); + } print_function_trftypes(&buf, proctup); - appendStringInfo(&buf, "\n LANGUAGE %s\n", + appendStringInfo(&buf, " LANGUAGE %s\n", quote_identifier(get_language_name(proc->prolang, false))); /* Emit some miscellaneous options on one line */ @@ -2607,10 +2616,11 @@ pg_get_functiondef(PG_FUNCTION_ARGS) * * Since the user is likely to be editing the function body string, we * shouldn't use a short delimiter that he might easily create a conflict - * with. Hence prefer "$function$", but extend if needed. + * with. Hence prefer "$function$"/"$procedure$", but extend if needed. */ initStringInfo(&dq); - appendStringInfoString(&dq, "$function"); + appendStringInfoChar(&dq, '$'); + appendStringInfoString(&dq, (isfunction ? "function" : "procedure")); while (strstr(prosrc, dq.data) != NULL) appendStringInfoChar(&dq, 'x'); appendStringInfoChar(&dq, '$'); diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 873907dc434..e7bede24faa 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -30,6 +30,17 @@ CALL ptest1(substring(random()::text, 1, 1)); -- ok, volatile arg public | ptest1 | | x text | proc (1 row) +SELECT pg_get_functiondef('ptest1'::regproc); + pg_get_functiondef +--------------------------------------------------- + CREATE OR REPLACE PROCEDURE public.ptest1(x text)+ + LANGUAGE sql + + AS $procedure$ + + INSERT INTO cp_test VALUES (1, x); + + $procedure$ + + +(1 row) + SELECT * FROM cp_test ORDER BY b COLLATE "C"; a | b ---+------- diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index d65e568a64e..774c12ee34b 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -17,6 +17,7 @@ CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg CALL ptest1(substring(random()::text, 1, 1)); -- ok, volatile arg \df ptest1 +SELECT pg_get_functiondef('ptest1'::regproc); SELECT * FROM cp_test ORDER BY b COLLATE "C"; -- 2.39.5