BUG #13799: Unexpected multiple exection of user defined function with out parameters
От | mike.lang1010@gmail.com |
---|---|
Тема | BUG #13799: Unexpected multiple exection of user defined function with out parameters |
Дата | |
Msg-id | 20151205063635.2620.17495@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #13799: Unexpected multiple exection of user defined
function with out parameters
("Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de>)
Re: BUG #13799: Unexpected multiple exection of user defined function with out parameters ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: mike.lang1010@gmail.comДата:
Сообщение: BUG #13798: Unexpected multiple exection of user defined function with out parameters
Следующее
От: shuklaabhishek02@gmail.comДата:
Сообщение: BUG #13806: Installing postgresql-plperl-9.4 gives error