CODEWITHSUNDEEP
databasepostgresqlproceduretemp-tables
user2346047
user2346047

Reputation: 243

Temporary Tables in PostgreSQL

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

  1. A() having a temporary table say temp(id, name)
  2. 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???

Upvotes: 0

Views: 1397

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 0

Beryllium
Beryllium

Reputation: 12998

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).

Upvotes: 1

Related Questions