Skip site navigation (1) Skip section navigation (2)

simulating global temp tables in plpgsql functions

From: Dennis Sacks <dennis(at)illusions(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: simulating global temp tables in plpgsql functions
Date: 2004-12-28 00:20:06
Message-ID: 41D0A6B6.7060701@illusions.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hi,

Once again I'm translating an oracle stored proc that uses a global 
temporary table. Using postgresql's nonglobal temp tables from plpgsql 
functions is painful - translating all the queries into strings passed 
to execute. It is error prone, and it makes the queries less readable, 
thus less maintainable. Plus, the temp tables have to be created per 
postgresql connection, and not recreated if they already exist (making 
connection pooling code more complicated).

So, the other option is to create a normal table and insert & query with 
a unique key, to avoid collisions with other copies of the stored 
function that might be executing at the same time. Is anyone else doing 
this? Does anyone have suggestions for how this might be accomplished 
safely?

Finally, is there any thought to adding global temp tables to 
postgresql? They would make life SO much easier for those of us porting 
from Oracle.

Dennis
dennis(at)illusions(dot)com

pgsql-sql by date

Next:From: Joachim ZobelDate: 2004-12-28 15:05:53
Subject: Re: Get current trasanction id
Previous:From: Marek LewczukDate: 2004-12-27 19:54:49
Subject: Re: Get current trasanction id

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group