Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to cast to regprocedure with OUT parameters

How to cast to regprocedure with OUT parameters

From
Thomas Kellerer
Date:
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?

Thanks
Thomas

Re: How to cast to regprocedure with OUT parameters

From
Tom Lane
Date:
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

Re: How to cast to regprocedure with OUT parameters

From
Thomas Kellerer
Date:
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
SQLWorkbench/J 

Thanks for the quick reply
Thomas