How Does TEMP Table Work In Plpgsql?

Hi!

I am trying to create a function returning 2 values
using temporary table as "media":

database1=# CREATE function f2values(numeric,numeric)
returns bool as '
database1'# begin
database1'# if $1 >= 1 then
database1'# create temp table mytemp(a numeric,b
numeric);
database1'# insert into mytemp values
($1+1,$2+5);
database1'# return 1;
database1'# else
database1'# return 0;
database1'# end if;
database1'# end;' language 'plpgsql';
CREATE
database1=# CREATE function test() returns bool as '
database1'# declare
database1'# r1 numeric;
database1'# r2 numeric;
database1'# begin
database1'# if f2values(1,1) then
database1'# select a,b into r1,r2 from mytemp;
database1'# raise notice ''%,%'',r1,r2;
database1'# drop table mytemp;
database1'# else
database1'# return 0;
database1'# end if;
database1'# return 1;
database1'# end;' language 'plpgsql';
CREATE

Now do the test:

database1=# select test();
NOTICE: 2.000000,6.000000
test
------
t
(1 row)

database1=# select test();
ERROR: Relation 782255 does not exist
database1=#

Now try a more simple one:

database1=# CREATE function test2(numeric,numeric)
returns bool as '
database1'# declare
database1'# r1 numeric;
database1'# r2 numeric;
database1'# begin
database1'# create temp table mytemp(a numeric,b
numeric);
database1'# insert into mytemp values
($1+1,$2+5);
database1'# select a,b into r1,r2 from mytemp;
database1'# raise notice ''%,%'',r1,r2;
database1'# drop table mytemp;
database1'# return 1;
database1'# end;' language 'plpgsql';
CREATE
database1=# select test2(1,1);
NOTICE: 2.000000,6.000000
test2
-------
t
(1 row)

database1=# select test2(1,1);
ERROR: Relation 782230 does not exist
database1=#

Weid is that test2 and test work only once per psql
connection (i.e. "session", am I correct?).
Can anyone help?

Thanks!

CN

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com