[SQL] Problem with temporary table -- Urgent

[Vijay Kumar]

Hi,
We are using postgresql 7.3.3, we are encountering 
the following problems when we used temporary tables. 
 
Here with i'm sending my Sample function. 

 
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;
 
psql:test18.sql:25: 
WARNING:  Error occurred while executing PL/pgSQL function 
testtemp_refcurpsql:test18.sql:25: WARNING:  line 20 at 
openpsql:test18.sql:25: ERROR:  Relation "temp_table" does not 
existpsql:test18.sql:26: ERROR:  current transaction is aborted, 
queries ignored until end of transaction block
Kindly guide as to solve this 
problem
 
Any help will be highly 
appreciated
 
Thanks & Regards
Vijay
 

[SQL] problem with temporary table.

[Vijay Kumar]

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