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

On Sat, Dec 5, 2015 at 7:36 AM, <mike(dot)lang1010(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13799
> Logged by: Michael Lang
> Email address: mike(dot)lang1010(at)gmail(dot)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