Обсуждение: BUG #13799: Unexpected multiple exection of user defined function with out parameters

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.

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.  To
demonstrate that this is happening I've provided the following example:

```
CREATE TABLE test (
  i integer
);

INSERT into test (i) VALUES (0);

CREATE FUNCTION reproduceBehavior(
  OUT message1 text,
  OUT message2 text,
  OUT message3 text,
  OUT message4 text
)
AS $$
DECLARE t integer;
BEGIN
  SELECT i INTO t FROM test limit 1;
  IF t = 0 THEN
    update test set i=1;
    message1 := 'The value of i is now 1';
  END IF;
  IF t = 1 THEN
    update test set i=2;
    message2 := 'The value of i is now 2';
  END IF;
  IF t = 2 THEN
    update test set i=3;
    message3 := 'The value of i is now 3';
  END IF;
  IF t = 3 THEN
    update test set i=4;
    message4 := 'The value of i is now 4';
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT (reproduceBehavior()).*;
```

Which produces the result:

```
        message1         |        message2         |        message3
|        message4
-------------------------+-------------------------+-------------------------+-------------------------
 The value of i is now 1 | The value of i is now 2 | The value of i is now 3
| The value of i is now 4
(1 row)
```

I've reproduced this behavior on:
PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

and

 PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

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

On Fri, Dec 4, 2015 at 11:36 PM, <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 bu=
g.
> Please discard bug #13798 in favor of this post.
> ---
>

=E2=80=8BAnswers provided on original (#13798) bug report thread.

In short:

Use LATERAL
Use CTE (i.e., WITH) if unable or undesirable to use LATERAL=E2=80=8B

=E2=80=8BUsing FROM works as long as you don't need to pass in parameters f=
rom
other relations.  Typically this problem occurs when you do which is why
putting the function call into the SELECT list happens in the first place.=
=E2=80=8B

David J.

On Tue, Dec 8, 2015 at 4:54 AM, Shulgin, Oleksandr <
oleksandr.shulgin@zalando.de> wrote:

>
> You can try:
>
> SELECT * FROM demo();
>
> I think that is the idiomatic way to do that.
>

Thanks, Alex.  I can't believe I didn't try that.

--
Mike