knowing which table/schema is going to be chosen

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