problem with temporary table. - Mailing list pgsql-sql
| From | Vijay Kumar |
|---|---|
| Subject | problem with temporary table. |
| Date | |
| Msg-id | 000c01c346ca$7fc0b660$cf01a8c0@vijay Whole thread Raw |
| Responses |
Re: problem with temporary table.
(Jonathan Gardner <jgardner@jonathangardner.net>)
|
| List | pgsql-sql |
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