Thread: Stable function Repeatedly Called

Hi!

drop table tt1;
create table tt1 (c1 text,c2 text);
drop table tt2;
create table tt2 (c1 text,c2 text);
insert into tt1 values('a','aa');
insert into tt1 values('b','bb');
insert into tt1 values('c','cc');
insert into tt2 values('x','a');
insert into tt2 values('y','b');

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' STABLE;

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

I thought function "f1" would get called only once but actually it is called 3 times. How do I force it to be called
onlyonce in the same SQL? 
--
_______________________________________________
Get your free email from http://www.graffiti.net

Powered by Outblaze

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

- Richard Huxton

On Fri, 2003-01-10 at 04:54, dev@archonet.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

Thank you! Robert,

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

Indeed this fix the problem.
However, when this is in rule, function f1 is called repeatedly again. Any idea?

Regards,

CN

--
_______________________________________________
Get your free email from http://www.graffiti.net

Powered by Outblaze

> However, when this is in rule, function f1 is called repeatedly again.

For example,

create table tt1 (c1 text,c2 text);
create table tt2 (c1 text,c2 text);
insert into tt1 values('a','aa');
insert into tt1 values('b','bb');
insert into tt1 values('c','cc');
insert into tt2 values('x','a');
insert into tt2 values('y','b');

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' STABLE;

CREATE RULE MyRule AS ON INSERT TO AnyTable DO update tt1 set c2='ABC' WHERE c1=(select f1('x'));

Thank you!

CN
--
_______________________________________________
Get your free email from http://www.graffiti.net

Powered by Outblaze