Re: Schemas: status report, call for developers - Mailing list pgsql-hackers

On Wednesday 01 May 2002 06:38, Tom Lane wrote:
> Ian Barwick <barwick@gmx.net> writes:
> > How can I restrict the query to the schemas in the
> > current search path, i.e. the schema names returned
> > by SELECT current_schemas() ?
>
> Well, this is the issue open for public discussion.
>
> We could define some function along the lines of
> "is_visible_table(oid) returns bool", and then you could use
> that as a WHERE clause in your query.  But I'm worried about
> the performance implications --- is_visible_table() would have
> to do several retail probes of the system tables, and I don't
> see any way to optimize that across hundreds of table OIDs.
>
> I have a nagging feeling that this could be answered by defining
> a view on pg_class that only shows visible tables ... but I don't
> quite see how to define that efficiently, either.  Ideas anyone?

(time passes...)

How about a function such as the one attached: "select_schemas_setof()"
which returns the OIDs of the schemas in the current search path as
a set. (Note: "select_schemas_setof()" as shown is a userspace C function.)

It works like this:
 template1=# CREATE DATABASE schema_test; CREATE DATABASE template1=# \c schema_test You are now connected to database
schema_test.schema_test=# CREATE OR REPLACE FUNCTION current_schemas_setof() schema_test-# RETURNS setof OID
schema_test-#as '/path/to/current_schemas_setof.so' schema_test-# LANGUAGE 'C'; CREATE FUNCTION 


I can then do this:
 schema_test=# CREATE SCHEMA foo; CREATE SCHEMA schema_test=# CREATE TABLE foo.mytab(col1 int, col2 text); CREATE TABLE
schema_test=#CREATE SCHEMA bar; CREATE SCHEMA schema_test=# CREATE TABLE bar.mytab(col1 int, col2 text); CREATE TABLE
schema_test=#SET search_path = public, foo, bar; SET schema_test=# SELECT current_schemas(); current_schemas
------------------{public,foo,bar} (1 row) 
 schema_test=# SELECT current_schemas_setof, n.nspname schema_test-#   FROM public.current_schemas_setof() cs,
pg_namespacen schema_test-#  WHERE cs.current_schemas_setof = n.oid; current_schemas_setof | nspname
----------------------+------------                16563 | pg_temp_1                    11 | pg_catalog
2200 | public                 24828 | foo                 24835 | bar (3 rows) 


With the function in place I can then create an SQL function like this:
 CREATE OR REPLACE FUNCTION public.first_visible_namespace(name) RETURNS oid AS 'SELECT n.oid    FROM pg_namespace n,
pg_classc, public.current_schemas_setof() cs   WHERE c.relname= $1     AND c.relnamespace=n.oid     AND n.oid=
cs.current_schemas_setof  LIMIT 1' LANGUAGE 'sql'; 

which can be used like this:
 schema_test=# select public.first_visible_namespace('mytab'); first_visible_namespace -------------------------
          24828 (1 row) 

i.e. finds the first visible schema containing an unqualified relation name.
24828 corresponds to the OID of schema "foo".

The following VIEW:
 CREATE VIEW public.desc_table_view AS SELECT n.nspname AS "Schema",       c.relname AS "Table",       a.attname AS
"Column",      format_type    (a.atttypid, a.atttypmod) AS "Type" FROM pg_class c, pg_attribute a, pg_namespace n WHERE
a.attnum> 0   AND c.relkind IN ('r', 'v', 'S')   AND a.attrelid = c.oid   AND c.relnamespace=n.oid   AND n.oid IN
(SELECTfirst_visible_namespace(c.relname)) ORDER BY a.attnum; 

then provides a simplified simulation of psql's slash command \d [NAME] for
unqualified relation names, e.g.:
 schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" = 'mytab'; Schema | Table | Column |  Type
--------+-------+--------+---------foo    | mytab | col1   | integer foo    | mytab | col2   | text (2 rows)
schema_test=#SET search_path= bar, foo, public; SET schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" =
'mytab';Schema | Table | Column |  Type --------+-------+--------+--------- bar    | mytab | col1   | integer bar    |
mytab| col2   | text (2 rows) 
 schema_test=# SET search_path= public; SET schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" = 'mytab';
Schema| Table | Column | Type --------+-------+--------+------ (0 rows) 


which I think is the desired behaviour. Currently \d [NAME] produces this: schema_test=# SET search_path= bar, foo,
public;SET schema_test=# \d mytab         Table "mytab" Column |  Type   | Modifiers--------+---------+----------- col1
 | integer | col1   | integer | col2   | text    | col2   | text    | 

i.e. finds and describes "foo.mytab" and "bar.mytab".

(Note: "SELECT * FROM public.desc_table_view" will just dump an unordered
list of all columns for the first visible instance of each table name).

Assuming "current_schemas_setof()" can be implemented as an internal function,
(I haven't managed it myself yet :-( ), I suspect it is a more efficient
alternative to a putative "is_visible_table(oid)" and could be used in psql
(and elsewhere) to resolve the schemas of unqualified relation names.
Thoughts? (Or am I barking up the wrong tree?)

BTW is anyone working on schema support in psql? If the various definition
issues raised by Tom Lane at the start of this thread are resolved (discussion
seems to have trailed off without a consensus), I have some free time in June
and would be willing to take it on.


Ian Barwick