Обсуждение: knowing which table/schema is going to be chosen

I may have several tables with the same name in different schema.

pina.mytable, test.mytable, import.mytable

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;

In this case test.mytable will be chosen.

Is there a way to ask postgresql the schema of the table that will be
chosen?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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
);

--
  Richard Huxton
  Archonet Ltd

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

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> 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: ...

"By default" means "assuming you're using the default search_path".
However saying that here, where we haven't explained search_path yet,
wouldn't be an improvement IMO.

            regards, tom lane