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 only once 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