Postgres Pro
Downloads
Home   >   mailing lists

Thread: Temporary Table Problem

Temporary Table Problem

From
"Dinesh Parikh"
Date:
Dear all,
I have a strange problem.Document says that one can create a temporary table in a session. I had created a temporary table in languge plpgsql.(Actually in a procedure). After completing my task I droped a table within a seesion(In same Function). Now problem arises. When I again calles that stored procedure (Function) then it says relation does not exist . What may be the cause of that problem. It is urgent. I need ed thi function as internal function so it called again and again.
 
I had attached Code as well as error.
 
I am Using RedHat 7.1, Postgresql 7.1.2
 
Thanks
Dinesh Parikh
NSTL, New Delhi
 
 
Function code.
Drop Function TestTempTable()
Create Function TestTempTable()
Returns Int4 As '
Declare
    DBFirst    Int4;
    DBRec      Record;
Begin
    Create Temporary Table Dual(FirstCol Int4);
    Insert Into Dual Values(12);     
    Insert Into Dual Values(13);     
    Insert Into Dual Values(14);     
    For DBRec In
        Select FirstCol From Dual
    Loop
        Raise Notice ''Column Value = %'',DBRec.FirstCol;
    End Loop;
    Drop Table Dual;
    Return 0;
End;
'language 'plpgsql';
 
Error Appeared. 
 
When First time run
 
Jurassik=# select TestTempTable();
NOTICE:  Column Value = 12
NOTICE:  Column Value = 13
NOTICE:  Column Value = 14
 testtemptable
---------------
             0
(1 row)
When Second time run
 
Jurassik=# select TestTempTable();
ERROR:  Relation 161414 does not exist
 

Re: Temporary Table Problem

From
Stephan Szabo
Date:
IIRC, any things where you're doing creates/drops
in plpgsql pretty much mean you have to generate
the statements as strings and use EXECUTE which
will prevent the plans from being cached.

On Fri, 28 Sep 2001, Dinesh Parikh wrote:

> Dear all, I have a strange problem.Document says that one can create a
> temporary table in a session. I had created a temporary table in
> languge plpgsql.(Actually in a procedure). After completing my task I
> droped a table within a seesion(In same Function). Now problem arises.
> When I again calles that stored procedure (Function) then it says
> relation does not exist . What may be the cause of that problem. It is
> urgent. I need ed thi function as internal function so it called again
> and again.