Re: stable function called for every row?

Hello,

stable, volatile, immutable flag doesn't necessary means caching or
not caching.

if you need really only one call, use srf function

postgres=# create table foo(a int);
CREATE TABLE
postgres=# insert into foo values(10),(20);
INSERT 0 2
postgres=# create function foof(bool) returns setof int as $$begin
raise notice 'foof call'; if $1 then return next 1; else return next
0; end if; return; end;$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from foof(true);
NOTICE: foof call
foof
------
1
(1 row)

postgres=# select * from foo,foof(true);
NOTICE: foof call
a | foof
----+------
10 | 1
20 | 1
(2 rows)

regards
Pavel Stehule

2009/1/7 Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de>:
> I isolated my problem a little bit:
>
> CREATE FUNCTION get_array() RETURNS integer[] AS
> $BODY$
> BEGIN
> RAISE INFO 'get_array';
> RETURN ARRAY[1, 2];
> END
> $BODY$ LANGUAGE 'plpgsql' STABLE;
>
> And now
>
> SELECT * FROM generate_series(1,3) a(b) where array[b] <@ core.get_array();
>
> gives me:
>
> INFO: get_array
> INFO: get_array
> INFO: get_array
> b
> ---
> 1
> 2
> (2 rows)
>
> Why?? Wlli functions which returns an array not be cached?
>
> Regards,
> Gerhard
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJZQ2Ea8fhU24j2fkRAlChAKCDTbhPdKxschTqScfhqRb5olvQ5wCcCcgl
> iMUlTPHTmX0jX/G84Pk82iA=
> =b/pY
> -----END PGP SIGNATURE-----
>
>