Re: Schemas: status report, call for developers

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> So, how does one determine the current schema for temporary tables,
> i.e. what name would be in search_path if it wasn't implicitly included?

The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array
slot number). AFAIK there isn't any exported way to determine your
BackendId from an SQL query. Another problem is that the pg_temp
schema is "lazily evaluated" --- it's not actually attached to and
cleaned out until you first try to create a temp table in a particular
session. This seems a clear win from a performance point of view,
but it makes life even more difficult for queries that are trying to
determine which pg_class entries are visible in one's search path.

I have already had occasion to write subroutines that answer the
question "is this relation (resp. type, function, operator) visible
in the current search path?" --- where visible means not just that
its namespace is in the path, but that this object is the frontmost
entry of its particular name. Perhaps it'd make sense to export these
routines as SQL functions, along the lines of "relation_is_visible(oid)
returns bool". Then one could use queries similar to

select * from pg_class p
where p.relname like 'match_pattern'
and relation_is_visible(p.oid);

to implement a psql command that requires finding tables matching
an (unqualified) relation-name pattern. The tables found would be
only those that you could reference with unqualified table names.

This doesn't yield much insight about cases where the match pattern
includes a (partial?) schema-name specification, though. If I'm
allowed to write something like "\z s*.t*" to find tables beginning
with t in schemas beginning with s, should that include all schemas
beginning with s? Only those in my search path (probably wrong)?
Only those that I have USAGE privilege on? Not sure.

regards, tom lane