Re: temp tables ORACLE/PGSQL

NO-fisher-SPAM_PLEASE wrote:

>Hi
>I used to work with Oracle and now tryin' PostgreSQL I'm a bit
>confused.
>I found that creating temp table in one session does not make it
>available for other sessions for the same user? Is this intended??
>
>
PostgreSQL does not support global temporary tables. This is one of the
most painful features missing as far as porting from Oracle goes from my
standpoint.

Yes, you need to create the temporary table at the beginning of each
session. Also, stored procedures that use temporary tables are more
painful to write - you need to use EXECUTE for any SQL that references a
temporary table - read the Porting From Oracle section of the PostgreSQL
manual. I'd recommend rereading it several times.

The other option with temporary tables is to emulate a global temporary
table using a normal table and adding a column like this:

session_id INTEGER DEFAULT pg_backend_pid() NOT NULL

and then modifying your select/update/delete statements to include
"where session_id = pg_backend_pid()" so that you only deal with the
data from your current session.

The pg_backend_pid() guaranteed to be unique while connected. You'll
just want to make sure you have a process for deleting rows from the
table so if you get a pg_backend_pid() again you won't have problems.

This has the advantage of not having to create a temporary table at the
beginning of every session, plus your stored procedures don't need to
use EXECUTE. The disadvantage is, you'll have to have some process for
deleting old data from the table, as it will stay around and it will
bite you when you get the same pg_backend_pid() again down the road.

Dennis