Re: knowing which table/schema is going to be chosen

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