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@gheift.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-----
>
>