Re: Schemas: status report, call for developers

Ian Barwick <barwick(at)gmx(dot)net> writes:
> 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=3D $1
> AND c.relnamespace=3Dn.oid
> AND n.oid=3D cs.current_schemas_setof
> LIMIT 1'
> LANGUAGE 'sql';

I don't believe this is correct. The LIMIT clause will ensure you
get at most one answer, but it'd be pure luck whether it is the right
answer, when there are multiple tables of the same name in the
namespaces of the search path.

> 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=09(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 =3D c.oid
> AND c.relnamespace=3Dn.oid
> AND n.oid IN (SELECT first_visible_namespace(c.relname))
> ORDER BY a.attnum;

I was hoping to find something more efficient than that --- quite aside
from the speed or correctness of first_visible_namespace(), a query
depending on an IN is not going to be fast.

regards, tom lane