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
