From: | Ingmar Brouns <swingi(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | volatile void returning function not executed as often as expected in sql function |
Date: | 2013-05-15 10:34:57 |
Message-ID: | CA+77E=Z5AM4NJVf5d009JZpDpFf3ezunUntvot9+1+CnJsip_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ingmar Brouns | 2013-05-15 10:44:33 | Re: volatile void returning function not executed as often as expected in sql function |
Previous Message | Raghavendra | 2013-05-15 10:33:39 | Re: pg_basebackup -R option in PG 9.3 beta |