Drop temporary tables in a single session

Question

How to drop the temporary tables in procedures for the same session.

Because I am facing a problem regarding temporary tables in Postgres Sql. If two procedures:

A() having a temporary table say temp(id, name) B() having a temporary table say temp(id, name, address) then if the procedure A is called first and after that procedure B is called then the temp table remains with the structure i.e. temp(id, name) defined in the procedure A and vice versa and the column "address" as defined in procedure B is not found.

If you procedure calls are in different transactions you can use transectiona level temporary tables. — Ihor Romanchenko, May 22 '13 at 10:09

score 1 · Answer 1 · answered May 22 '13 at 09:50

1

PostgreSQL doesn't support procedure level temporary tables, so you just cannot do it.

A usage of temporary tables in Pg is little bit different than in other db. In your case, you have to use different names.

answered May 22 '13 at 09:50

Pavel Stehule

42,331
5
91
94

If i am using on commit drop then it works or not or is there is any other alternative. – ripunj2408813 May 22 '13 at 10:39
if you will never call A from B or B from A, then it can works, but it is bad idea usually. – Pavel Stehule May 22 '13 at 10:51
Yes both the procedures A and B are independent of each other,why this idea is bad? – ripunj2408813 May 22 '13 at 11:17
a) it hard to be sure, so A and B are independently called always - this is some other expectation in your application (it my personal option. I don't like hidden dependencies). b) repeated creating and dropping temp tables does traffic in system tables - and you will lost cached execution plans every call. The best usage of temp table in pg is creating one, when doesn't exists and delete content before usage when exists (on procedure start) – Pavel Stehule May 22 '13 at 12:20

score 0 · Accepted Answer · answered May 23 '13 at 04:41

Temp tables are session scoped. Meaning the same session can see it across procedure calls and SQL calls. Basically the temp table is global to your session.

So your options are this assuming A calls B:

Call procedure A and create temp table within A and do what you want with it. At the end of procedure A drop the temp table. Call B from A, you can now create the temporary table with the same name inside of B and make use of it. As a good habit explicitly drop temp table once your're done with it.
Otherwise use two different names for the temp table.

http://www.postgresql.org/docs/9.2/static/sql-createtable.html "TEMPORARY or TEMP If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well."

Drop temporary tables in a single session

2 Answers2