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.
NoteThe 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: