Thread: Temporary Table Problem

Temporary Table Problem

From

"Dinesh Parikh"

Date:

29 September 2001, 07:00:16

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