Temporary Tables in PostgreSQL

Question

I am facing a problem regarding global 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.

Please help me to find a solution???

So you want procedure-local temporary tables so that A's and B's temp tables would not be visible to the other procedure? — mu is too short
– mu is too short, Commented May 22, 2013 at 6:03
i mean to say if we make a database call for A first, then temp table is created with columns id and name. after that if we make a database call for B then the temp table defined in B is not there, it takes the temp table as defined in A with columns id and name. the column address as defined in temp table of B is not found. — user2346047
– user2346047, Commented May 22, 2013 at 6:21
You want the procedures to share the same temp table but the procedures need it to have different columns? Or do you want them to share it but create it (with different columns) if someone else hasn't created it already? Both seem a bit odd to me. — mu is too short
– mu is too short, Commented May 22, 2013 at 6:43

Beryllium · Accepted Answer · 2013-05-22 09:33:47Z

1

If you really need to have explicit temporary tables, just create these with a unique name.

Anyway, the common approaches would be to handle it in SQL selects without explicit temporary tables, possibly extended by using with queries (common table expressions).

answered May 22, 2013 at 9:33

Beryllium

13k11 gold badges59 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beryllium Over a year ago

Not that I am aware of. Three alternatives are not enough :-) Describe your use case a bit further.

Chris Travers · Accepted Answer · 2013-05-29 07:45:38Z

0

Basically you have two options.

The first is to make your tables unique so that they don't hit the same ones. This would be preferred if you are using these to store longer-term session-specific data. These could be named uniquely.

A second is that you can create, use, and drop your tables inside the same stored procedure so that stored procedure A can be generally guaranteed that relation temp does not exist when starting and the same with stored procedure B.

answered May 29, 2013 at 7:45

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

Temporary Tables in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related