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.

Note

The role that creates a temporary schema-object must have the temporary privilege on the current database.

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: