The following bug has been logged online:
Bug reference: 5872
Logged by: Rodolfo Campero
Email address: rodolfo.campero@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
