How to cast to regprocedure with OUT parameters

Thomas Kellerer <spam_eater@gmx.net> writes:

Hello,
pg_get_functiondef() can be used quite easily by using a cast to regprocedure, like this:

select pg_get_functiondef('public.foo(text, text, text)'::regprocedure);

However if the function is defined with an out parameter like this:

create or replace function foo(p1 text, p2 out text, p3 text)
....

the above cast does not work. Postgres returns an error: "function public.foo(text,text,text) does not exist.

'public.foo(text, OUT text, text)'::regprocedure does not work either.

So, what should be the right format to write the string literal that can be cast to the correct regprocedure?

Leave out the OUT parameters altogether:

select pg_get_functiondef('public.foo(text, text)'::regprocedure);

Only IN parameters contribute to the function's identity; OUT parameters
are just a variant method of specifying its return type.

Personally I wouldn't randomly mix IN and OUT like that, but put all the
OUT parameters at the end of the list. It seems too confusing otherwise.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote on 14.08.2014 17:33:

Leave out the OUT parameters altogether:

select pg_get_functiondef('public.foo(text, text)'::regprocedure);

Only IN parameters contribute to the function's identity; OUT parameters
are just a variant method of specifying its return type.

Ah, great. I didn't think of that.

Personally I wouldn't randomly mix IN and OUT like that, but put all the
OUT parameters at the end of the list. It seems too confusing otherwise.

I agree ;) and I'm not creating functions like that.

I'm just trying to tackle all possible combinations in order to reliably retrieve a functions source code in my tool SQL Workbench/J

Thanks for the quick reply
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general