How Does TEMP Table Work In Plpgsql? - Mailing list pgsql-general

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