Persistent temp tables in Postgres
Solution to implement persistent temp tables in Postgres
Problem
One of the problems that has to be resolved during Oracle 2 Postgres code migration is handling of temporary tables. Biggest difference is that in Oracle temporary tables are persistent schema level objects. You define the table structure once and it exists until you will explicitly drop it. The only difference comparing to ordinary table is that data in temporary table are stored either during current transaction only or until end of current session.
In contrast in Postgres temp tables are pure temporary objects and got automatically dropped after current session end. They also support data lifetime duration on the transaction or session level. But no temp table is available upon start of a new session.
In PL/SQL code you might have any SELECT or DML statement issued against temp table.
They will work because it will be present as a schema object.
In migrated plpgsql code such temp tables references will fail with relation "table_name" does not exist error.
We have to somehow create the table before executing any statement against it.
