volatile void returning function not executed as often as expected in sql function - Mailing list pgsql-general

Hi,

I have a volatile void returning function that I call in the SELECT
clause of a query. When I execute the query, the function is called
for every row in the result, this is also what I expect. However, if I
embed that same query in an sql function and then call that function
it gets executed only once. This is not what I expect, am I missing
something?

test case:
----------------------------------------------------------------------

create table foo(a int);
insert into foo values (0),(10),(100);

create or replace function foofunc (a_in int) returns void as
$func$
begin
    update foo set a = a+1 where a = a_in;
end;
$func$ language plpgsql;
--increase every a in foo by 1
select foofunc(a) from foo;
--as exected, all records are increased
select * from foo;

--increase every a in foo by 1, but now in function
create or replace function foofunc2() returns void as
$func$
    select foofunc(a) from foo
$func$ language sql;
select foofunc2();
--only one record is increased, this is not what I expect
select * from foo;

--cleanup
drop table foo;
drop function foofunc(int);
drop function foofunc2();

*************************************************************************
output:
*************************************************************************

pv=# create table foo(a int);
CREATE TABLE
pv=# insert into foo values (0),(10),(100);
INSERT 0 3
pv=#
pv=# create or replace function foofunc (a_in int) returns void as
pv-# $func$
pv$# begin
pv$#     update foo set a = a+1 where a = a_in;
pv$# end;
pv$# $func$ language plpgsql;
CREATE FUNCTION
pv=# --increase every a in foo by 1
pv=# select foofunc(a) from foo;
 foofunc
---------



(3 rows)

pv=# --as exected, all records are increased
pv=# select * from foo;
  a
-----
   1
  11
 101
(3 rows)

pv=#
pv=# --increase every a in foo by 1, but now in function
pv=# create or replace function foofunc2() returns void as
pv-# $func$
pv$#     select foofunc(a) from foo
pv$# $func$ language sql;
CREATE FUNCTION
pv=# select foofunc2();
 foofunc2
----------

(1 row)

pv=# --only one record is increased, this is not what I expect
pv=# select * from foo;
  a
-----
  11
 101
   2
(3 rows)


pv=# select pg_version();
 pg_version
------------
 9.2.4
(1 row)



Thanks,

Ingmar