On 2/1/18 11:33, Pavel Stehule wrote:
> postgres=# \sf test
> ERROR: cache lookup failed for type 0
Here is a patch set that adds procedure support to \ef and \sf.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From ffa37952aac6562f78fbed6f3d73a02913b42b89 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 12 Feb 2018 13:47:18 -0500
Subject: [PATCH 1/3] Fix typo
---
src/test/regress/expected/create_function_3.out | 144 ++++++++++++------------
src/test/regress/sql/create_function_3.sql | 88 +++++++--------
2 files changed, 116 insertions(+), 116 deletions(-)
diff --git a/src/test/regress/expected/create_function_3.out
b/src/test/regress/expected/create_function_3.out
index b5e19485e5..2fd25b8593 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -69,124 +69,124 @@ SELECT proname, provolatile FROM pg_proc
--
-- SECURITY DEFINER | INVOKER
--
-CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
-CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_2(int) RETURNS bool LANGUAGE 'sql'
SECURITY DEFINER AS 'SELECT $1 = 0';
-CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_3(int) RETURNS bool LANGUAGE 'sql'
SECURITY INVOKER AS 'SELECT $1 < 0';
SELECT proname, prosecdef FROM pg_proc
- WHERE oid in ('functext_C_1'::regproc,
- 'functext_C_2'::regproc,
- 'functext_C_3'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_C_1'::regproc,
+ 'functest_C_2'::regproc,
+ 'functest_C_3'::regproc) ORDER BY proname;
proname | prosecdef
--------------+-----------
- functext_c_1 | f
- functext_c_2 | t
- functext_c_3 | f
+ functest_c_1 | f
+ functest_c_2 | t
+ functest_c_3 | f
(3 rows)
-ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect
-ALTER FUNCTION functext_C_2(int) SECURITY INVOKER;
-ALTER FUNCTION functext_C_3(int) SECURITY DEFINER;
+ALTER FUNCTION functest_C_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_C_2(int) SECURITY INVOKER;
+ALTER FUNCTION functest_C_3(int) SECURITY DEFINER;
SELECT proname, prosecdef FROM pg_proc
- WHERE oid in ('functext_C_1'::regproc,
- 'functext_C_2'::regproc,
- 'functext_C_3'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_C_1'::regproc,
+ 'functest_C_2'::regproc,
+ 'functest_C_3'::regproc) ORDER BY proname;
proname | prosecdef
--------------+-----------
- functext_c_1 | f
- functext_c_2 | f
- functext_c_3 | t
+ functest_c_1 | f
+ functest_c_2 | f
+ functest_c_3 | t
(3 rows)
--
-- LEAKPROOF
--
-CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 100';
-CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_2(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 > 100';
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
proname | proleakproof
--------------+--------------
- functext_e_1 | f
- functext_e_2 | t
+ functest_e_1 | f
+ functest_e_2 | t
(2 rows)
-ALTER FUNCTION functext_E_1(int) LEAKPROOF;
-ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_E_1(int) LEAKPROOF;
+ALTER FUNCTION functest_E_2(int) STABLE; -- unrelated change, no effect
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
proname | proleakproof
--------------+--------------
- functext_e_1 | t
- functext_e_2 | t
+ functest_e_1 | t
+ functest_e_2 | t
(2 rows)
-ALTER FUNCTION functext_E_2(int) NOT LEAKPROOF; -- remove leakproog
attribute
+ALTER FUNCTION functest_E_2(int) NOT LEAKPROOF; -- remove leakproog
attribute
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
proname | proleakproof
--------------+--------------
- functext_e_1 | t
- functext_e_2 | f
+ functest_e_1 | t
+ functest_e_2 | f
(2 rows)
-- it takes superuser privilege to turn on leakproof, but not for turn off
-ALTER FUNCTION functext_E_1(int) OWNER TO regress_unpriv_user;
-ALTER FUNCTION functext_E_2(int) OWNER TO regress_unpriv_user;
+ALTER FUNCTION functest_E_1(int) OWNER TO regress_unpriv_user;
+ALTER FUNCTION functest_E_2(int) OWNER TO regress_unpriv_user;
SET SESSION AUTHORIZATION regress_unpriv_user;
SET search_path TO temp_func_test, public;
-ALTER FUNCTION functext_E_1(int) NOT LEAKPROOF;
-ALTER FUNCTION functext_E_2(int) LEAKPROOF;
+ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF;
+ALTER FUNCTION functest_E_2(int) LEAKPROOF;
ERROR: only superuser can define a leakproof function
-CREATE FUNCTION functext_E_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 < 200'; -- failed
ERROR: only superuser can define a leakproof function
RESET SESSION AUTHORIZATION;
--
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--
-CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 50';
-CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_2(int) RETURNS bool LANGUAGE 'sql'
CALLED ON NULL INPUT AS 'SELECT $1 = 50';
-CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_3(int) RETURNS bool LANGUAGE 'sql'
RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
-CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE 'sql'
STRICT AS 'SELECT $1 = 50';
SELECT proname, proisstrict FROM pg_proc
- WHERE oid in ('functext_F_1'::regproc,
- 'functext_F_2'::regproc,
- 'functext_F_3'::regproc,
- 'functext_F_4'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_F_1'::regproc,
+ 'functest_F_2'::regproc,
+ 'functest_F_3'::regproc,
+ 'functest_F_4'::regproc) ORDER BY proname;
proname | proisstrict
--------------+-------------
- functext_f_1 | f
- functext_f_2 | f
- functext_f_3 | t
- functext_f_4 | t
+ functest_f_1 | f
+ functest_f_2 | f
+ functest_f_3 | t
+ functest_f_4 | t
(4 rows)
-ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect
-ALTER FUNCTION functext_F_2(int) STRICT;
-ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT;
+ALTER FUNCTION functest_F_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_F_2(int) STRICT;
+ALTER FUNCTION functest_F_3(int) CALLED ON NULL INPUT;
SELECT proname, proisstrict FROM pg_proc
- WHERE oid in ('functext_F_1'::regproc,
- 'functext_F_2'::regproc,
- 'functext_F_3'::regproc,
- 'functext_F_4'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_F_1'::regproc,
+ 'functest_F_2'::regproc,
+ 'functest_F_3'::regproc,
+ 'functest_F_4'::regproc) ORDER BY proname;
proname | proisstrict
--------------+-------------
- functext_f_1 | f
- functext_f_2 | t
- functext_f_3 | f
- functext_f_4 | t
+ functest_f_1 | f
+ functest_f_2 | t
+ functest_f_3 | f
+ functest_f_4 | t
(4 rows)
-- information_schema tests
@@ -236,15 +236,15 @@ drop cascades to function functest_a_3()
drop cascades to function functest_b_2(integer)
drop cascades to function functest_b_3(integer)
drop cascades to function functest_b_4(integer)
-drop cascades to function functext_c_1(integer)
-drop cascades to function functext_c_2(integer)
-drop cascades to function functext_c_3(integer)
-drop cascades to function functext_e_1(integer)
-drop cascades to function functext_e_2(integer)
-drop cascades to function functext_f_1(integer)
-drop cascades to function functext_f_2(integer)
-drop cascades to function functext_f_3(integer)
-drop cascades to function functext_f_4(integer)
+drop cascades to function functest_c_1(integer)
+drop cascades to function functest_c_2(integer)
+drop cascades to function functest_c_3(integer)
+drop cascades to function functest_e_1(integer)
+drop cascades to function functest_e_2(integer)
+drop cascades to function functest_f_1(integer)
+drop cascades to function functest_f_2(integer)
+drop cascades to function functest_f_3(integer)
+drop cascades to function functest_f_4(integer)
drop cascades to function functest_b_2(bigint)
DROP USER regress_unpriv_user;
RESET search_path;
diff --git a/src/test/regress/sql/create_function_3.sql
b/src/test/regress/sql/create_function_3.sql
index 0a0e407aab..6c411bdfda 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -52,57 +52,57 @@ CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE
'sql'
--
-- SECURITY DEFINER | INVOKER
--
-CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
-CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_2(int) RETURNS bool LANGUAGE 'sql'
SECURITY DEFINER AS 'SELECT $1 = 0';
-CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_C_3(int) RETURNS bool LANGUAGE 'sql'
SECURITY INVOKER AS 'SELECT $1 < 0';
SELECT proname, prosecdef FROM pg_proc
- WHERE oid in ('functext_C_1'::regproc,
- 'functext_C_2'::regproc,
- 'functext_C_3'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_C_1'::regproc,
+ 'functest_C_2'::regproc,
+ 'functest_C_3'::regproc) ORDER BY proname;
-ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect
-ALTER FUNCTION functext_C_2(int) SECURITY INVOKER;
-ALTER FUNCTION functext_C_3(int) SECURITY DEFINER;
+ALTER FUNCTION functest_C_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_C_2(int) SECURITY INVOKER;
+ALTER FUNCTION functest_C_3(int) SECURITY DEFINER;
SELECT proname, prosecdef FROM pg_proc
- WHERE oid in ('functext_C_1'::regproc,
- 'functext_C_2'::regproc,
- 'functext_C_3'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_C_1'::regproc,
+ 'functest_C_2'::regproc,
+ 'functest_C_3'::regproc) ORDER BY proname;
--
-- LEAKPROOF
--
-CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 100';
-CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_2(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 > 100';
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
-ALTER FUNCTION functext_E_1(int) LEAKPROOF;
-ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_E_1(int) LEAKPROOF;
+ALTER FUNCTION functest_E_2(int) STABLE; -- unrelated change, no effect
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
-ALTER FUNCTION functext_E_2(int) NOT LEAKPROOF; -- remove leakproog
attribute
+ALTER FUNCTION functest_E_2(int) NOT LEAKPROOF; -- remove leakproog
attribute
SELECT proname, proleakproof FROM pg_proc
- WHERE oid in ('functext_E_1'::regproc,
- 'functext_E_2'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_E_1'::regproc,
+ 'functest_E_2'::regproc) ORDER BY proname;
-- it takes superuser privilege to turn on leakproof, but not for turn off
-ALTER FUNCTION functext_E_1(int) OWNER TO regress_unpriv_user;
-ALTER FUNCTION functext_E_2(int) OWNER TO regress_unpriv_user;
+ALTER FUNCTION functest_E_1(int) OWNER TO regress_unpriv_user;
+ALTER FUNCTION functest_E_2(int) OWNER TO regress_unpriv_user;
SET SESSION AUTHORIZATION regress_unpriv_user;
SET search_path TO temp_func_test, public;
-ALTER FUNCTION functext_E_1(int) NOT LEAKPROOF;
-ALTER FUNCTION functext_E_2(int) LEAKPROOF;
+ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF;
+ALTER FUNCTION functest_E_2(int) LEAKPROOF;
-CREATE FUNCTION functext_E_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 < 200'; -- failed
RESET SESSION AUTHORIZATION;
@@ -110,28 +110,28 @@ CREATE FUNCTION functext_E_3(int) RETURNS bool LANGUAGE
'sql'
--
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--
-CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 50';
-CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_2(int) RETURNS bool LANGUAGE 'sql'
CALLED ON NULL INPUT AS 'SELECT $1 = 50';
-CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_3(int) RETURNS bool LANGUAGE 'sql'
RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
-CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
+CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE 'sql'
STRICT AS 'SELECT $1 = 50';
SELECT proname, proisstrict FROM pg_proc
- WHERE oid in ('functext_F_1'::regproc,
- 'functext_F_2'::regproc,
- 'functext_F_3'::regproc,
- 'functext_F_4'::regproc) ORDER BY proname;
-
-ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect
-ALTER FUNCTION functext_F_2(int) STRICT;
-ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT;
+ WHERE oid in ('functest_F_1'::regproc,
+ 'functest_F_2'::regproc,
+ 'functest_F_3'::regproc,
+ 'functest_F_4'::regproc) ORDER BY proname;
+
+ALTER FUNCTION functest_F_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functest_F_2(int) STRICT;
+ALTER FUNCTION functest_F_3(int) CALLED ON NULL INPUT;
SELECT proname, proisstrict FROM pg_proc
- WHERE oid in ('functext_F_1'::regproc,
- 'functext_F_2'::regproc,
- 'functext_F_3'::regproc,
- 'functext_F_4'::regproc) ORDER BY proname;
+ WHERE oid in ('functest_F_1'::regproc,
+ 'functest_F_2'::regproc,
+ 'functest_F_3'::regproc,
+ 'functest_F_4'::regproc) ORDER BY proname;
-- information_schema tests
base-commit: 80f021ef139affdb219ccef71fff283e8f91f112
--
2.16.1
From cd94f187228be3e66c741469681a5703413eecef Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 12 Feb 2018 14:03:04 -0500
Subject: [PATCH 2/3] Add tests for pg_get_functiondef
---
src/test/regress/expected/create_function_3.out | 44 +++++++++++++++++++++++++
src/test/regress/sql/create_function_3.sql | 8 +++++
2 files changed, 52 insertions(+)
diff --git a/src/test/regress/expected/create_function_3.out
b/src/test/regress/expected/create_function_3.out
index 2fd25b8593..5ff1e0dd86 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -189,6 +189,50 @@ SELECT proname, proisstrict FROM pg_proc
functest_f_4 | t
(4 rows)
+-- pg_get_functiondef tests
+SELECT pg_get_functiondef('functest_A_1'::regproc);
+ pg_get_functiondef
+--------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_a_1(text, date)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ AS $function$SELECT $1 = 'abcd' AND $2 > '2001-01-01'$function$ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_B_3'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_b_3(integer)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ STABLE +
+ AS $function$SELECT $1 = 0$function$ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_C_3'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_c_3(integer)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ SECURITY DEFINER +
+ AS $function$SELECT $1 < 0$function$ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_F_2'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_f_2(integer)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ STRICT +
+ AS $function$SELECT $1 = 50$function$ +
+
+(1 row)
+
-- information_schema tests
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
RETURNS int
diff --git a/src/test/regress/sql/create_function_3.sql
b/src/test/regress/sql/create_function_3.sql
index 6c411bdfda..fbdf8310e3 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -134,6 +134,14 @@ CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE
'sql'
'functest_F_4'::regproc) ORDER BY proname;
+-- pg_get_functiondef tests
+
+SELECT pg_get_functiondef('functest_A_1'::regproc);
+SELECT pg_get_functiondef('functest_B_3'::regproc);
+SELECT pg_get_functiondef('functest_C_3'::regproc);
+SELECT pg_get_functiondef('functest_F_2'::regproc);
+
+
-- information_schema tests
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
--
2.16.1
From 3d615285d8cb500eb01f3f6c2f7f9084f01cc9a7 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 12 Feb 2018 14:33:49 -0500
Subject: [PATCH 3/3] Add procedure support to pg_get_functiondef
This also makes procedures work in psql's \ef and \sf commands.
Reported-by: Pavel Stehule <pavel.steh...@gmail.com>
---
doc/src/sgml/func.sgml | 8 ++++----
doc/src/sgml/ref/psql-ref.sgml | 10 ++++++----
src/backend/utils/adt/ruleutils.c | 19 +++++++++++++------
src/test/regress/expected/create_procedure.out | 11 +++++++++++
src/test/regress/sql/create_procedure.sql | 1 +
5 files changed, 35 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 640ff09a7b..4be31b082a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17008,22 +17008,22 @@ <title>System Catalog Information Functions</title>
<row>
<entry><literal><function>pg_get_functiondef(<parameter>func_oid</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
- <entry>get definition of a function</entry>
+ <entry>get definition of a function or procedure</entry>
</row>
<row>
<entry><literal><function>pg_get_function_arguments(<parameter>func_oid</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
- <entry>get argument list of function's definition (with default
values)</entry>
+ <entry>get argument list of function's or procedure's definition (with
default values)</entry>
</row>
<row>
<entry><literal><function>pg_get_function_identity_arguments(<parameter>func_oid</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
- <entry>get argument list to identify a function (without default
values)</entry>
+ <entry>get argument list to identify a function or procedure (without
default values)</entry>
</row>
<row>
<entry><literal><function>pg_get_function_result(<parameter>func_oid</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
- <entry>get <literal>RETURNS</literal> clause for function</entry>
+ <entry>get <literal>RETURNS</literal> clause for function (returns null
for a procedure)</entry>
</row>
<row>
<entry><literal><function>pg_get_indexdef(<parameter>index_oid</parameter>)</function></literal></entry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6f9b30b673..8bd9b9387e 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1815,8 +1815,9 @@ <title>Meta-Commands</title>
<listitem>
<para>
- This command fetches and edits the definition of the named function,
- in the form of a <command>CREATE OR REPLACE FUNCTION</command>
command.
+ This command fetches and edits the definition of the named function
or procedure,
+ in the form of a <command>CREATE OR REPLACE FUNCTION</command> or
+ <command>CREATE OR REPLACE PROCEDURE</command> command.
Editing is done in the same way as for <literal>\edit</literal>.
After the editor exits, the updated command waits in the query buffer;
type semicolon or <literal>\g</literal> to send it, or
<literal>\r</literal>
@@ -2970,8 +2971,9 @@ <title>Meta-Commands</title>
<listitem>
<para>
- This command fetches and shows the definition of the named function,
- in the form of a <command>CREATE OR REPLACE FUNCTION</command>
command.
+ This command fetches and shows the definition of the named function
or procedure,
+ in the form of a <command>CREATE OR REPLACE FUNCTION</command> or
+ <command>CREATE OR REPLACE PROCEDURE</command> command.
The definition is printed to the current query output channel,
as set by <command>\o</command>.
</para>
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 28767a129a..9eec24281d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2477,15 +2477,21 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
* replaced.
*/
nsp = get_namespace_name(proc->pronamespace);
- appendStringInfo(&buf, "CREATE OR REPLACE FUNCTION %s(",
+ appendStringInfo(&buf, "CREATE OR REPLACE %s %s(",
+ proc->prorettype ? "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 (proc->prorettype)
+ {
+ 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 +2613,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, (proc->prorettype ? "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 873907dc43..e7bede24fa 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 d65e568a64..774c12ee34 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -17,6 +17,7 @@ CREATE PROCEDURE ptest1(x text)
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.16.1
