Обсуждение: 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

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

The reason is that the query plans are getting cached.
AFAIK, if you want to work with temp tables in plpgsql,
you're going to need to use EXECUTE to generate all the
queries that relate to the temp table.