From: | "Shulgin, Oleksandr" <oleksandr(dot)shulgin(at)zalando(dot)de> |
---|---|
To: | mike(dot)lang1010(at)gmail(dot)com |
Cc: | Pg Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #13799: Unexpected multiple exection of user defined function with out parameters |
Date: | 2015-12-08 10:54:50 |
Message-ID: | CACACo5QXTC5wSeONv=bSAwd_MMzOJPX=8FbP2zdc1-TSyTNrJQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2015-12-08 14:44:40 | Re: Incorrect UPDATE trigger invocation in the UPDATE clause of an UPSERT statement. |
Previous Message | Terje Elde | 2015-12-08 07:57:45 | Re: BUG #13805: plpgsql execute using expression evaluate wrong |