Обсуждение: How can I find a schema that a table belong to?

Hi,

I am trying to tidy up my perl script that runs as a cgi and allows
remote users (aka me) to interact with my Pg database.

I primarily want to  use this tool as a way for my iPad to
browse my data base. It seems to be working quite well but
one certainly does not want to 'select * from ginormous_table'.

I am trying to build a 'describe' function. ie if the user
types "describe tablename" in the sql box I want to display
some interesting attributes for that table, mainly names and
data types for each of the columns.

I have done this in other programs but I forced the user
to specify a schema qualified name ( and mimiced what psql -E
did).

So I guess the question is:
   Given a bare table name, how can I recover the schema
   qualified name with whatever the current search path happens
   to be?

   This task has to be done using  simple sql from the perl dbi.

Thanks,

Jerry

Jerry LeVan, 19.01.2011 17:35:
>
> So I guess the question is:
>     Given a bare table name, how can I recover the schema
>     qualified name with whatever the current search path happens
>     to be?
>

SELECT table_schema
FROM information_schema.tables
WHERE table_name = 'your_table'
;

Thomas Kellerer <spam_eater@gmx.net> writes:
> Jerry LeVan, 19.01.2011 17:35:
>> So I guess the question is:
>> Given a bare table name, how can I recover the schema
>> qualified name with whatever the current search path happens
>> to be?

> SELECT table_schema
> FROM information_schema.tables
> WHERE table_name = 'your_table'
> ;

That's not going to work, at least not in the interesting case where you
have more than one candidate table --- that SELECT will list all of 'em.

In most cases the answer to this type of problem is "use regclass",
but regclass doesn't quite solve Jerry's problem because it won't
schema-qualify the name if the table is visible in the search path.
The best solution I can think of is

select nspname from pg_namespace n join pg_class c on n.oid = c.relnamespace
  where c.oid = 'my_table_name'::regclass;

which works but seems a bit brute-force.

            regards, tom lane

Tom Lane, 19.01.2011 19:19:
>> SELECT table_schema
>> FROM information_schema.tables
>> WHERE table_name = 'your_table'
>> ;
>
> That's not going to work, at least not in the interesting case where you
> have more than one candidate table --- that SELECT will list all of 'em.
>

Ah, right. I was a buit too quick with my answer.

Regards
Thomas

Tom Lane, 19.01.2011 19:19:
>>> Given a bare table name, how can I recover the schema
>>> qualified name with whatever the current search path happens
>>> to be?
>
>> SELECT table_schema
>> FROM information_schema.tables
>> WHERE table_name = 'your_table'
>> ;
>
> That's not going to work, at least not in the interesting case where you
> have more than one candidate table --- that SELECT will list all of 'em.

What about something like this:

SELECT tbl.table_schema, tbl.table_name, pe.path_position
FROM information_schema.tables tbl
   JOIN (
     SELECT path_element, row_number() over () as path_position
     FROM (
       SELECT trim(unnest(string_to_array(setting, ','))) as path_element
       FROM pg_settings
       WHERE name = 'search_path'
     ) t
   ) pe on tbl.table_schema = pe.path_element
WHERE tbl.table_name = 'your_table'
ORDER BY pe.path_position;

This will list each table together with the index of the schema in the search path in the order of the schemas listed
inthe search path. 

The only thing I'm unsure about is whether unnest() will always preserve the order of the array.

Regards
Thomas