Creating and using temporary schema-objects

A temporary schema-object can be created at any time during a session's lifetime and lasts for no longer than the session.

Apart from their limited lifetime, temporary schema-objects are largely the same, semantically, as their permanent counterparts. But there are critical differences:

• A temporary table's content is private to the session that created it. (By extension, the content of an index on a temporary table is private too.) Moreover, a temporary table uniquely supports the use the special syntax on commit delete rows (see the create table section).

• You can see metadata about one session's temporary objects from another session, for as long as the first session lasts. But no session except the one that created a temporary object can use it.

Here are some scenarios where temporary schema-objects are useful.

• Oracle Database supports a schema-object kind called package. A package encapsulates user-defined subprograms together with package-level global variables. Such variables have session duration and the values are private within a single session. But PostgreSQL, and therefore YSQL, have no package construct. A one-column, one-row temporary table can be used to model a scalar package global variable; a one-column, multi-row temporary table can be used to model an array of scalars; and a multi-column, multi-row temporary table can be used to model an array of user-defined type occurrences.
• Oracle Database supports its equivalent of PostgreSQL's prepare-and-execute paradigm for anonymous PL/SQL blocks as well as for regular DML statements. But PostgreSQL's prepare statement supports only regular DML statements and not the do statement. In Oracle Database, parameterized anonymous PL/SQL blocks are used when the encapsulated steps need to be done several times in a session, binding in different actual arguments each time, during some kind of set up flow, but never need to be done again. A temporary language plpgsql procedure in PostgreSQL, and therefore in YSQL, meets this use case perfectly.
• See the section Porting from Oracle PL/SQL in the PostgreSQL documentation.

Look, now, at each of the following child sections:

• Temporary tables, views, and sequences

• Creating temporary schema-objects of all kinds

• Demonstrate the globality of metadata, and the privacy of use of temporary objects

• Recommended on-demand paradigm for creating temporary objects