Here's a before and after version of a simplified version of what
you're trying to do in your script. The 'after' version uses 'execute'
and 'for row in exectue ... loop' to not cache plans. Also notice the
use of a temporary table, visible only to this backend, in case more
than one backend calls the function simultaneously.
Any other discussion should be done off of this list, as this is not
JDBC related at all.
James
-- simplified version of inital plpgsql function. Gets bitten by cached
query plan.
create or replace function f() returns setof record as '
DECLARE
row RECORD;
BEGIN
create table foo
(
i int
);
insert into foo values(1);
insert into foo values(2);
FOR row in select * from foo LOOP
RETURN NEXT row;
END LOOP;
drop table foo;
return;
END;
' LANGUAGE 'plpgsql';
-- works
select * from f() as f_results(id int);
-- fails on any subsequent call in this session
select * from f() as f_results(id int);
-- now a version that will work more than once per session
-- note the use of execute and for ... in execute
create or replace function f() returns setof record as '
DECLARE
row RECORD;
BEGIN
create temporary table foo
(
i int
);
execute ''insert into foo values(1)'';
execute ''insert into foo values(2)'';
FOR row in execute ''select * from foo'' LOOP
RETURN NEXT row;
END LOOP;
drop table foo;
return;
END;
' LANGUAGE 'plpgsql';
-- now can call many times per session
select * from f() as f_results(id int);
select * from f() as f_results(id int);
select * from f() as f_results(id int);
select * from f() as f_results(id int);
----
James Robinson
Socialserve.com
