Skip to content

Commit 7a32ac8

Browse files
peterepetere
committed
Add procedure support to pg_get_functiondef
This also makes procedures work in psql's \ef and \sf commands. Reported-by: Pavel Stehule <pavel.stehule@gmail.com>
1 parent 7cd56f2 commit 7a32ac8

File tree

5 files changed

+38
-14
lines changed

5 files changed

+38
-14
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -17008,22 +17008,22 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1700817008
<row>
1700917009
<entry><literal><function>pg_get_functiondef(<parameter>func_oid</parameter>)</function></literal></entry>
1701017010
<entry><type>text</type></entry>
17011-
<entry>get definition of a function</entry>
17011+
<entry>get definition of a function or procedure</entry>
1701217012
</row>
1701317013
<row>
1701417014
<entry><literal><function>pg_get_function_arguments(<parameter>func_oid</parameter>)</function></literal></entry>
1701517015
<entry><type>text</type></entry>
17016-
<entry>get argument list of function's definition (with default values)</entry>
17016+
<entry>get argument list of function's or procedure's definition (with default values)</entry>
1701717017
</row>
1701817018
<row>
1701917019
<entry><literal><function>pg_get_function_identity_arguments(<parameter>func_oid</parameter>)</function></literal></entry>
1702017020
<entry><type>text</type></entry>
17021-
<entry>get argument list to identify a function (without default values)</entry>
17021+
<entry>get argument list to identify a function or procedure (without default values)</entry>
1702217022
</row>
1702317023
<row>
1702417024
<entry><literal><function>pg_get_function_result(<parameter>func_oid</parameter>)</function></literal></entry>
1702517025
<entry><type>text</type></entry>
17026-
<entry>get <literal>RETURNS</literal> clause for function</entry>
17026+
<entry>get <literal>RETURNS</literal> clause for function (returns null for a procedure)</entry>
1702717027
</row>
1702817028
<row>
1702917029
<entry><literal><function>pg_get_indexdef(<parameter>index_oid</parameter>)</function></literal></entry>

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1815,8 +1815,9 @@ Tue Oct 26 21:40:57 CEST 1999
18151815

18161816
<listitem>
18171817
<para>
1818-
This command fetches and edits the definition of the named function,
1819-
in the form of a <command>CREATE OR REPLACE FUNCTION</command> command.
1818+
This command fetches and edits the definition of the named function or procedure,
1819+
in the form of a <command>CREATE OR REPLACE FUNCTION</command> or
1820+
<command>CREATE OR REPLACE PROCEDURE</command> command.
18201821
Editing is done in the same way as for <literal>\edit</literal>.
18211822
After the editor exits, the updated command waits in the query buffer;
18221823
type semicolon or <literal>\g</literal> to send it, or <literal>\r</literal>
@@ -2970,8 +2971,9 @@ testdb=&gt; <userinput>\setenv LESS -imx4F</userinput>
29702971

29712972
<listitem>
29722973
<para>
2973-
This command fetches and shows the definition of the named function,
2974-
in the form of a <command>CREATE OR REPLACE FUNCTION</command> command.
2974+
This command fetches and shows the definition of the named function or procedure,
2975+
in the form of a <command>CREATE OR REPLACE FUNCTION</command> or
2976+
<command>CREATE OR REPLACE PROCEDURE</command> command.
29752977
The definition is printed to the current query output channel,
29762978
as set by <command>\o</command>.
29772979
</para>

‎src/backend/utils/adt/ruleutils.c

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2449,6 +2449,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
24492449
StringInfoData dq;
24502450
HeapTuple proctup;
24512451
Form_pg_proc proc;
2452+
bool isfunction;
24522453
Datum tmp;
24532454
bool isnull;
24542455
const char *prosrc;
@@ -2472,20 +2473,28 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
24722473
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
24732474
errmsg("\"%s\" is an aggregate function", name)));
24742475

2476+
isfunction = (proc->prorettype != InvalidOid);
2477+
24752478
/*
24762479
* We always qualify the function name, to ensure the right function gets
24772480
* replaced.
24782481
*/
24792482
nsp = get_namespace_name(proc->pronamespace);
2480-
appendStringInfo(&buf, "CREATE OR REPLACE FUNCTION %s(",
2483+
appendStringInfo(&buf, "CREATE OR REPLACE %s %s(",
2484+
isfunction ? "FUNCTION" : "PROCEDURE",
24812485
quote_qualified_identifier(nsp, name));
24822486
(void) print_function_arguments(&buf, proctup, false, true);
2483-
appendStringInfoString(&buf, ")\n RETURNS ");
2484-
print_function_rettype(&buf, proctup);
2487+
appendStringInfoString(&buf, ")\n");
2488+
if (isfunction)
2489+
{
2490+
appendStringInfoString(&buf, " RETURNS ");
2491+
print_function_rettype(&buf, proctup);
2492+
appendStringInfoChar(&buf, '\n');
2493+
}
24852494

24862495
print_function_trftypes(&buf, proctup);
24872496

2488-
appendStringInfo(&buf, "\n LANGUAGE %s\n",
2497+
appendStringInfo(&buf, " LANGUAGE %s\n",
24892498
quote_identifier(get_language_name(proc->prolang, false)));
24902499

24912500
/* Emit some miscellaneous options on one line */
@@ -2607,10 +2616,11 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
26072616
*
26082617
* Since the user is likely to be editing the function body string, we
26092618
* shouldn't use a short delimiter that he might easily create a conflict
2610-
* with. Hence prefer "$function$", but extend if needed.
2619+
* with. Hence prefer "$function$"/"$procedure$", but extend if needed.
26112620
*/
26122621
initStringInfo(&dq);
2613-
appendStringInfoString(&dq, "$function");
2622+
appendStringInfoChar(&dq, '$');
2623+
appendStringInfoString(&dq, (isfunction ? "function" : "procedure"));
26142624
while (strstr(prosrc, dq.data) != NULL)
26152625
appendStringInfoChar(&dq, 'x');
26162626
appendStringInfoChar(&dq, '$');

‎src/test/regress/expected/create_procedure.out

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,17 @@ CALL ptest1(substring(random()::text, 1, 1)); -- ok, volatile arg
3030
public | ptest1 | | x text | proc
3131
(1 row)
3232

33+
SELECT pg_get_functiondef('ptest1'::regproc);
34+
pg_get_functiondef
35+
---------------------------------------------------
36+
CREATE OR REPLACE PROCEDURE public.ptest1(x text)+
37+
LANGUAGE sql +
38+
AS $procedure$ +
39+
INSERT INTO cp_test VALUES (1, x); +
40+
$procedure$ +
41+
42+
(1 row)
43+
3344
SELECT * FROM cp_test ORDER BY b COLLATE "C";
3445
a | b
3546
---+-------

‎src/test/regress/sql/create_procedure.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
1717
CALL ptest1(substring(random()::text, 1, 1)); -- ok, volatile arg
1818

1919
\df ptest1
20+
SELECT pg_get_functiondef('ptest1'::regproc);
2021

2122
SELECT * FROM cp_test ORDER BY b COLLATE "C";
2223

0 commit comments

Comments
 (0)