Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7a32ac8

Browse files
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 parent7cd56f2 commit7a32ac8

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'sor procedure'sdefinition (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 functionor 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
StringInfoDatadq;
24502450
HeapTupleproctup;
24512451
Form_pg_procproc;
2452+
boolisfunction;
24522453
Datumtmp;
24532454
boolisnull;
24542455
constchar*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_testORDER BY b COLLATE"C";
2223

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp