Re: Schemas: status report, call for developers

On Wednesday 01 May 2002 06:38, Tom Lane wrote:
> Ian Barwick <barwick(at)gmx(dot)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_namespace n
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_class c, 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 (SELECT first_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