Re: [PATCH] Add a test for pg_get_functiondef()

From: Abhijit Menon-Sen <ams(at)toroid(dot)org>

Thanks to Andrew Gierth for writing the function used in the test.
---
src/test/regress/expected/defs.out | 43 ++++++++++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/defs.sql | 24 ++++++++++++++++++++
4 files changed, 69 insertions(+), 1 deletions(-)
create mode 100644 src/test/regress/expected/defs.out
create mode 100644 src/test/regress/sql/defs.sql

diff --git a/src/test/regress/expected/defs.out b/src/test/regress/expected/defs.out
new file mode 100644
index 0000000..5d0670a
--- /dev/null
+++ b/src/test/regress/expected/defs.out
@@ -0,0 +1,43 @@
+-- Test pg_get_functiondef()
+CREATE SCHEMA foo;
+SET search_path = public,foo,pg_catalog;
+CREATE DOMAIN foo."evil domain" as text;
+CREATE DOMAIN foo."date" as text;
+CREATE FUNCTION "$$evil"(out foo integer, inout bar date, in "evil domain", in anyelement)
+ returns setof record stable strict security definer cost 123 rows 2
+ language plpgsql as
+$f$
+ declare r record;
+ begin
+ for r in select * from "$$evil"(null,null,null) loop
+ foo := r.foo;
+ bar := r.bar;
+ return next;
+ end loop;
+ end;
+$f$;
+SELECT pg_get_functiondef('$$evil'::regproc::oid);
+ pg_get_functiondef
+--------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public."$$evil"(OUT foo integer, INOUT bar date, "evil domain", anyelement)
+ RETURNS SETOF record
+ LANGUAGE plpgsql
+ STABLE STRICT SECURITY DEFINER COST 123 ROWS 2
+ AS $function$
+ declare r record;
+ begin
+ for r in select * from "$$evil"(null,null,null) loop
+ foo := r.foo;
+ bar := r.bar;
+ return next;
+ end loop;
+ end;
+ $function$
+
+(1 row)
+
+DROP SCHEMA foo CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to type "evil domain"
+drop cascades to type date
+drop cascades to function "$$evil"(date,"evil domain",anyelement)
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3b1d843..f1e3dbc 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -77,7 +77,7 @@ test: misc
# ----------
# Another group of parallel tests
# ----------
-test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window
+test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window defs

# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index b7984ed..5dca5d3 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -121,3 +121,4 @@ test: with
test: xml
test: stats
test: tablespace
+test: defs
diff --git a/src/test/regress/sql/defs.sql b/src/test/regress/sql/defs.sql
new file mode 100644
index 0000000..cf8fff3
--- /dev/null
+++ b/src/test/regress/sql/defs.sql
@@ -0,0 +1,24 @@
+-- Test pg_get_functiondef()
+
+CREATE SCHEMA foo;
+SET search_path = public,foo,pg_catalog;
+CREATE DOMAIN foo."evil domain" as text;
+CREATE DOMAIN foo."date" as text;
+
+CREATE FUNCTION "$$evil"(out foo integer, inout bar date, in "evil domain", in anyelement)
+ returns setof record stable strict security definer cost 123 rows 2
+ language plpgsql as
+$f$
+ declare r record;
+ begin
+ for r in select * from "$$evil"(null,null,null) loop
+ foo := r.foo;
+ bar := r.bar;
+ return next;
+ end loop;
+ end;
+$f$;
+
+SELECT pg_get_functiondef('$$evil'::regproc::oid);
+
+DROP SCHEMA foo CASCADE;
--
1.6.1.2.253.ga34a

Sorry, I screwed up a little in sending that patch. Here it is again as
an attachment.

-- ams

Abhijit Menon-Sen <ams(at)oryx(dot)com> writes:
> [ a test whose purpose he didn't bother to describe ]

What is the value of this? It seems far more likely to cause
maintenance pain than to catch anything interesting.

regards, tom lane

At 2009-04-13 10:40:41 -0400, tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> Abhijit Menon-Sen <ams(at)oryx(dot)com> writes:
> > [ a test whose purpose he didn't bother to describe ]

I'm sorry about that.

> What is the value of this? It seems far more likely to cause
> maintenance pain than to catch anything interesting.

While I was writing pg_get_functiondef(), I found myself wishing for
just such a function (one with all the toppings, as it were) to make
sure I wasn't missing something. I thought it would be useful to keep
the function around to prevent inadvertent breakage, that's all. The
fact that the code was duplicated in pg_dump also worried me, though
this test case doesn't help with that.

What maintenance pain would it cause? It seems to me that if anything
causes this test to fail in future, it deserves a second look.

But it's a trivial patch, hardly worth spending much time discussing.
Perhaps its being in the mailing list archives is good enough.

-- ams