Creating temporary schema-objects of all kinds

You can create temporary schema-objects of all kinds, like composite types, functions, and operators. However, for historical reasons, there is no dedicated syntax that starts with create temporary except for tables, views, and sequences. Rather, you use the general approach that was shown in the section Temporary tables, views, sequences, and indexes that simply uses an identifier for the to-be-created temporary object that's qualified using the pg_temp alias. This ability is not called out specifically in the PostgreSQL documentation. But this turn in the thread "Create temporary function" on the pgsql-general email list, sent by Tom Lane (an authority on PostgreSQL's implementation), confirms the supported status of the ability.

Here are some examples. But first, look at the section Name resolution within top-level SQL statements. It recommends that you always mention pg_temp explicitly in the search_path and that you place it rightmost so that it is searched last. This implies that it's prudent always to refer to a temporary schema-object with a schema-qualified identifier that starts with pg_temp.

An unqualified identifier never resolves to a temporary procedure, a temporary function, or a temporary operator.

You must use a qualified identifier that starts with pg_temp to refer to a temporary procedure, a temporary function, or a temporary operator. The PostgreSQL designers think that, without this feature of the implementation, and when pg_temp is leftmost in the search_path (as is the default), the behavior of application code that referred to a permanent procedure, a permanent function, or a permanent operator could be subverted by capturing it name with a temporary schema-object of the same kind and with the same signature.

Yugabyte recommends that you insure yourself against name capture not only by temporary objects but also by permanent objects by always referring to schema-objects in application code with schema-qualified identifiers.

Start a new session as an ordinary role that has all privileges on the current database.

Then run each of the following examples. When you're done, look at the metadata for all of them.

Create a temporary view

The pg_temp.my_temporary_schema_objects temporary view lists all temporary schema-objects, of all kinds, that are owned by the current_role. As it happens, it doesn't need to be parameterized. But the need for a parameterizable temporary view would be met by encapsulating the select statement within a temporary language sql function with appropriate out parameters as well as the )in parameters that determine the results. Notice the functional equivalence between such a temporary function and a prepared SQL statement.

-- For example, connect as the role "d0$u0" to the database "d0".
\c d0 d0$u0

create view pg_temp.my_temporary_schema_objects(name, kind) as
  with o(name, kind, schema_oid, owner_oid) as
    (
      select
        relname,
        case relkind
          when 'r' then 'table'
          when 'v' then 'view'
          when 'i' then 'index'
          when 'S' then 'sequence'
          when 'c' then 'composite-type'
          when 't' then 'TOAST table'
          else          'other'
        end,
        relnamespace,
        relowner
      from pg_class
      -- Filter out the row that's automatically generated as the partner
      -- to a manually created composite type.
      where relkind <> 'c'

    union all
      select
        t.typname,
        'composite-type',
        t.typnamespace,
        t.typowner
      from
        pg_type as t
        inner join
        pg_class as c
        on t.typname = c.relname and t.typnamespace = c.relnamespace
      where t.typtype ='c'
      -- Filter out the row that's automatically generated as the partner
      -- to a manually created table or manually created view - or even a sequence!.
      and not (c.relkind = 'r' or c.relkind = 'v' or c.relkind = 'S')

    union all
      select
        typname,
        case typtype
          when 'd' then 'domain'
          when 'e' then 'enum'
          else          'other'
        end,
        typnamespace,
        typowner
      from pg_type t
      where (typtype = 'd' or typtype = 'e')

    union all
      select
        proname,
        case prokind
          when 'f' then 'function'
          when 'p' then 'procedure'
          when 'a' then 'aggregate'
          when 'w' then 'window'
        end,
        pronamespace,
        proowner
      from pg_proc

    union all
      select
        o.oprname,
        'operator',
        o.oprnamespace,
        o.oprowner
      from
        pg_operator as o
        inner join
        pg_proc as p
        on o.oprcode = p.oid
    )
select
  o.name,
  o.kind
 from
  o
  inner join
  pg_roles as r
  on o.owner_oid = r.oid
where r.rolname = current_role
and o.schema_oid = pg_my_temp_schema();

You can now simply query this view at any moment during the rest of the session's lifetime to see the temporary schema-objects that you have created to date.

Create a temporary composite type

create type pg_temp.cmplx_no as (real_cpt numeric, imagry_cpt numeric);

Create a temporary enum

create type pg_temp.colors as enum (
  'red', 'yellow', 'green', 'blue', 'indigo', 'violet');

Create a temporary function and temporary domain

create function pg_temp.is_positive(i in int)
  returns boolean
  language sql
as $body$
  select i > 0;
$body$;

create domain pg_temp.natural as int
constraint natural_ok check(pg_temp.is_positive(value));

Create a temporary function and temporary operator

create function pg_temp.cmplx_equals(
  a1 in pg_temp.cmplx_no,
  a2 in pg_temp.cmplx_no)
  returns boolean
  language sql
as $body$
  select (a1.real_cpt   = a2.real_cpt) and
         (a1.imagry_cpt = a2.imagry_cpt);
$body$;

create operator pg_temp.= (
  leftarg   = pg_temp.cmplx_no,
  rightarg  = pg_temp.cmplx_no,
  procedure = pg_temp.cmplx_equals);

select (
         (17, 42)::pg_temp.cmplx_no
         operator(pg_temp.=)
         (42, 17)::pg_temp.cmplx_no
       )::text;

This works, and produces the expected result: false. But notice the baroque syntax that you need to use the temporary equality operator. This reflects the fact that, as mentioned, an unqualified identifier is never resolved to a temporary operator.

Create a temporary table, a temporary index, a temporary sequence, and a temporary procedure

create table pg_temp.tab(k int, v int);
create index idx on pg_temp.tab(k);
create sequence pg_temp.tab_seq as int start with 10 increment by 5;

create procedure pg_temp.insert_tab(new_v in int)
  language plpgsql
  security definer
  set search_path = pg_catalog, pg_temp
as $body$
declare
  new_k constant int not null := (select nextval('tab_seq'));
begin
  insert into tab(k, v) values (new_k, new_v);
end;
$body$;

call pg_temp.insert_tab(42);
select * from pg_temp.tab;

This works—and produces this result:

 k  | v  
----+----
 10 | 42

But it exhibits very questionable style. Yugabyte recommends that you should routinely refer to every temporary schema-object, in all contexts, by a qualified identifier that starts with pg_temp. It recommends against reasoning (to the extent that you can depend on your reasoning) to establish when you can safely use an unqualified identifier for a temporary schema-object.

List the set of temporary schema-objects that have been created to date

select name, kind from pg_temp.my_temporary_schema_objects order by name;

This is the result:

            name             |      kind      
-----------------------------+----------------
 =                           | operator
 cmplx_equals                | function
 cmplx_no                    | composite-type
 colors                      | enum
 idx                         | index
 insert_tab                  | procedure
 is_positive                 | function
 my_temporary_schema_objects | view
 natural                     | domain
 tab                         | table
 tab_seq                     | sequence