On Thu, 12 Nov 2009 10:38:27 +0000 Richard Huxton <dev@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
