NO-fisher-SPAM_PLEASE wrote:
HiPostgreSQL 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.
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??
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
