The following bug has been logged online: Bug reference: 5872 Logged by: Rodolfo Campero Email address: rodolfo.camp...@anachronics.com PostgreSQL version: 8.4.5 Operating system: Debian 6.0 - Linux 2.6.32-5-amd64 Description: Function call in SQL function executed only once Details:
Hello, I stumbled upon a weird behavior of postgresql, I don't know if it's a bug or not, but I find it counterintuitive: when then last statement of a SQL function is a SELECT statement with calls another function, the call is executed only once, regardless of the number of rows returned by the FROM clause. This happens even if the called function is volatile. Here goes a test case: 8<----------------------------------------------- CREATE TABLE counter (cnt INTEGER NOT NULL); INSERT INTO counter VALUES (0); CREATE OR REPLACE FUNCTION increment_counter() RETURNS void AS $$ UPDATE counter SET cnt = cnt + 1; $$ LANGUAGE sql VOLATILE; CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ SELECT increment_counter() FROM generate_series(1,10); $$ LANGUAGE sql VOLATILE; postgres=# select test(); test ------ (1 row) postgres=# select cnt from counter; --should return 10 cnt ----- 1 (1 row) 8<----------------------------------------------- If a dummy statement is appended to the function body, we get the expected behavior: 8<----------------------------------------------- postgres=# CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ SELECT increment_counter() FROM generate_series(1,10); SELECT null::void; --this dummy SELECT works around the problem $$ LANGUAGE sql VOLATILE; CREATE FUNCTION postgres=# update counter set cnt = 0; UPDATE 1 postgres=# select test(); test ------ (1 row) postgres=# select cnt from counter; cnt ----- 10 (1 row) 8<----------------------------------------------- Maybe this was intended as an optimization for the case when a regular function calls a set-returning function (because the first row is returned and the remaining rows would get discarded anyway), but I think the call must be performed every time if the invoked function is volatile. Best regards, Rodolfo -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
