Re: Stable function Repeatedly Called

On Fri, 2003-01-10 at 04:54, dev(at)archonet(dot)com wrote:
> >
> > CREATE OR REPLACE FUNCTION f1(TEXT) RETURNS TEXT AS'
> > DECLARE
> > MyNumber TEXT;
> > BEGIN
> > SELECT c2 INTO MyNumber FROM tt2 WHERE c1 = $1;
> > RAISE NOTICE ''%'',MyNumber; --for debug
> > RETURN MyNumber;
> > END' LANGUAGE 'plpgsql' WITH (iscachable);
>
> Try adding the "WITH (iscachable)" to the end of the definition (instead
> of the STABLE that was there - is that an ORACLEism?). This tells PG that
> f1('x') always returns the same value.
>
> See the SQL reference for CREATE FUNCTION for details.
>

While that seems to work, it seems like it goes against what the
documentation stats:

(note, iscachable is the backward compatible way of using immutable)

IMMUTABLE indicates that the function always returns the same result
when given the same argument values; that is, it does not do database
lookups or otherwise use information not directly present in its
parameter list. If this option is given, any call of the function with
all-constant arguments can be immediately replaced with the function
value.

STABLE indicates that within a single table scan the function will
consistently return the same result for the same argument values, but
that its result could change across SQL statements. This is the
appropriate selection for functions whose results depend on database
lookups, parameter variables (such as the current time zone), etc. Also
note that the CURRENT_TIMESTAMP family of functions qualify as stable,
since their values do not change within a transaction.

Perhaps I am misinterpreting the docs, but given that your function does
a select from a table you should not be setting it IMMUTABLE.

I believe what you want to do is change your sql to

select * from tt1 where c1=(select f1('x'));
update tt1 set c2='ABC' WHERE c1=(select f1('x'));

I think there might be other benefits to this in regards to index usage
as well.

Robert Treat