Thread: problem with temporary table.
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;
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 ''''
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 || '' 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';
execute lString;
open refc for select * from temp_Table;
return refc;
end;
' language 'plpgsql';
begin;
select TestTemp_refcur('funcursor');
fetch all in funcursor;
commit;
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 10 July 2003 03:03, Vijay Kumar wrote: > 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. > < snip> > > 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. > So drop the table when you are done with it. You may also want to investigate returning a table of data, rather than a cursor or a single row. I am not too sure on how this would work exactly because I have never done it. Someone else may be willing to fill in the details. - -- Jonathan Gardner <jgardner@jonathangardner.net> (was jgardn@alumni.washington.edu) Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/FVY4WgwF3QvpWNwRAjIXAJ47L28D29zv91JGXQnA1rQ79wqRlwCeLB56 /+FhmG0tosyNXyH61po2myY= =UWtn -----END PGP SIGNATURE-----