BUG #13798: Unexpected multiple exection of user defined function with out parameters
От | mike.lang1010@gmail.com |
---|---|
Тема | BUG #13798: Unexpected multiple exection of user defined function with out parameters |
Дата | |
Msg-id | 20151205063015.2622.52326@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #13798: Unexpected multiple exection of user defined
function with out parameters
Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 13798 Logged by: Michael Lang Email address: mike.lang1010@gmail.com PostgreSQL version: 9.4.5 Operating system: Ubuntu 12.04 Description: 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 undesireable 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. 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()).*` produce 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 udf 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
В списке pgsql-bugs по дате отправления: