On Thu, 12 Nov 2009 10:38:27 +0000
Richard Huxton <dev@archonet.com> wrote:
> Ivan Sergio Borgonovo wrote:
> > I have a search_path that may not just consist of $user, public.
> > eg.
> > $user, public, test, import
> >
> > I'd like to know which table is going to be chosen if I do a
> > select * from mytable;
>
> > Is there a way to ask postgresql the schema of the table that
> > will be chosen?
>
> Hmm - I don't know of a specific function. You could do something
> like this though:
> SELECT nspname FROM pg_namespace
> WHERE oid = (
> SELECT relnamespace FROM pg_class
> WHERE oid = 'mytable'::regclass::oid
> );
This surely meet my needs, and I'm going to place it in my toolbox
still... is there a way that could use information_schema?
My need was caused by a compromise with 2 immature API... so I'm not
surprised that a solution looks like an hack but I was wondering if
in other cases knowing in advance which table postgresql is going to
pick up could be a legit interest.
BTW I think I've spotted an error in the docs:
http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html
http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html
5.7.2. The Public Schema
In the previous sections we created tables without specifying any
schema names. By default, such tables (and other objects) are
automatically put into a schema named "public". Every new database
contains such a schema. Thus, the following are equivalent: ...
CREATE TABLE products ( ... );
and:
CREATE TABLE public.products ( ... );
I think they are not equivalent if the search_path contains the name
of an existing schema.
Is there anything equivalent to search_path in the SQL standard?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
