Hi,
We are using postgresql7.3.3, we
are encountering some problems by using temporary tables.
Actually our requirement was,
1. create
temporary table.
2. insert
some values on that table by using some quries.
3.
select the inserted values from the temporary table.
To fullfil the above requirement, we wrote the
below functions..kindly go through the below functions and
guide us to come out from this temporary table
problem.
1. Call the same function more than ones in
the same connection.
eg;
create or replace function TestTemp_refcur(refcursor) returns
refcursor as '
declare
refc alias for $1;
begin
create temporary table temp_table(idno
numeric,iname varchar(10));
insert into temp_table values
(1,''ganesh'');
insert into temp_table values
(2,''John'');
open refc for select * from
test_temp_table;
return refc;
end;
' language
'plpgsql';
begin;
select TestTemp_refcur('funcursor'); fetch all in funcursor; commit; The above
function is working fine for the first call, from next call onwards it is
throwing the below error.
Error: relation 'temp_table' already exists.
2. To avoid this, we had gone through some
of postgresql faq and documents. There some one suggested to create
temporary table by Execute.
So that, we created one function,
inside that fucntion we created one more function to take care of creating
temporary table.
Eg,
create or replace
function TestTemp_refcur(refcursor) returns refcursor As '
declare refc alias for $1; lString Varchar(4000); begin lString := '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as '''' BEGIN
'';
lString := lString || '' create temporary table temp_Table( Name Numeric);'';
lString := lString || '' insert into temp_Table values (1);
'';
lString := lString || '' insert into temp_Table values
(2);'';
lString := lString || '' return null; end;'''' language
''''plpgsql'''';'';
raise notice '' Notice is % '',
lString;
execute lString; open refc for select * from temp_Table; return refc; end; ' language 'plpgsql';
begin;
select TestTemp_refcur('funcursor'); fetch all in funcursor; commit; With the above approach, we are getting the below
error.
Error :
Relation "temp_table" does not exist
Any kind of info/soln/help will be highly
appreciated..
Thanks & Regards
Vijay
|
- Re: [SQL] problem with temporary table. Vijay Kumar
- Re: [SQL] problem with temporary table. Jonathan Gardner