Re: Does CREATE FUNCTION... WITH (ISCACHABLE) work?

On 19 Jul 2000, at 14:30, Tom Lane wrote:

> "Joel Burton" <jburton@scw.org> writes:
> > I have a function that always returns the same answer given the same
> > input (no database lookups, etc.). The pg Users' Manual documents
> > the attribute 'iscachable' as allowing the database to parse the
> > results of the function and not keep looking it up.
>
> iscachable does not mean that the system will cache the results of the
> function across queries, it just means that the function needn't be
> re-evaluated multiple times for the same arguments within a single
> query. For example, given
>
>  SELECT * from table1 where col = foo(42);
>
> If foo() is marked cachable then it's evaluated once during query
> planning; if not it's evaluated again for each row scanned in table1.

Sounds reasonable. But does it work as advertised?

CREATE FUNCTION foo(int) RETURNS int AS '
BEGIN
  RAISE NOTICE ''hi'';
  RETURN 1;
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION foocache(int) RETURNS int AS '
BEGIN
  RAISE NOTICE ''hi'';
  RETURN 1;
END;'
LANGUAGE 'plpgsql'
WITH (iscachable);

SELECT foo(1),foo(1),foo(1)
gives us 3 NOTICEs, as does
SELECT foocache(1), foocache(1), foocache(1)

So is it running the cached version a second time?

Thanks,

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)