Re: BUG #13799: Unexpected multiple exection of user defined function with out parameters

On Sat, Dec 5, 2015 at 7:36 AM, <mike.lang1010@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13799
> Logged by:          Michael Lang
> Email address:      mike.lang1010@gmail.com
> PostgreSQL version: 9.4.5
> Operating system:   Ubuntu 12.04
> Description:
>
> Hi - it's late and I missed an typo in the previous submission of this bug.
> Please discard bug #13798 in favor of this post.
> ---
>
> I've found that when a user defined function has
> out parameters, it is invoked once per out parameter if invoked with the
> syntax:
>
> `SELECT (udf()).*`
>
> Is this the expected behavior?  It seems like it shouldn't.
>
> This syntax is desireable because it is the only way I've found so far to
> get the postgresql backend to return all of the out parameters together
> as a row, together with the parameters type information, instead of
> returning the out parameters together as the text representation of
> the composite type that they form together.
>
> For example, to demonstrate, take the function as follows:
> ```
> CREATE FUNCTION demo(
>   OUT param1 text,
>   OUT param2 text,
>   OUT param3 text
> ) AS $$
> BEGIN
>   param1 := 'foo';
>   param2 := 'bar';
>   param3 := 'baz';
> END;
> $$ LANGUAGE plpgsql
> ```
>
> The query `SELECT demo();` produces the result
> ```
> testdb=# SELECT demo();
>      demo
> ---------------
>  (foo,bar,baz)
> (1 row)
> ```
> Whereas the query `SELECT (demo()).*` produces the result
> ```
> testdb=# SELECT (demo()).*;
>  param1 | param2 | param3
> --------+--------+--------
>  foo    | bar    | baz
> (1 row)
> ```
>
> I've yet to find another means to get postgresql to produce the result
> in such a form.
>

You can try:

SELECT * FROM demo();

I think that is the idiomatic way to do that.

Unfortunately, I've found that the `SELECT (udf()).*` form executes the
> function once per out parameter.  This is undesirable for both performance
> reasons and unacceptable for functions that cause side effects.


Quite surprisingly, it does.  A simpler way to expose this behavior is by
use of RAISE statement:

=# CREATE OR REPLACE FUNCTION demo(
  OUT param1 text,
  OUT param2 text,
  OUT param3 text
) AS $$
BEGIN
  param1 := 'foo';
  param2 := 'bar';
  param3 := 'baz';
  RAISE NOTICE 'demo';
END;
$$ LANGUAGE plpgsql

=# select (demo()).*;
NOTICE:  demo
NOTICE:  demo
NOTICE:  demo
 param1 | param2 | param3
--------+--------+--------
 foo    | bar    | baz
(1 row)

--
Alex