********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
AND c.relname like '%my_table_name%'
ORDER BY 1,2;
**************************
On Wed, Jan 19, 2011 at 1:54 PM, DM <dm.aeqa@xxxxxxxxx> wrote:
If your looking for Views then you could use pg_views ==> select * from pg_views limit 1;That's not going to work, at least not in the interesting case where you
Here is one more, there was a recent post same as your request, Please see the below email, hope this helps you
Tom Lane =================>
Thomas Kellerer <spam_eater@xxxxxxx> writes:
> Jerry LeVan, 19.01.2011 17:35:> SELECT table_schema
>> 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?
> FROM information_schema.tables
> WHERE table_name = 'your_table'
> ;
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
- Hide quoted text -
==========================================>On Wed, Jan 19, 2011 at 12:58 PM, Jerry LeVan <jerry.levan@xxxxxxx> wrote:
That does not work if the user entered a 'view' in the sql box.
On Jan 19, 2011, at 3:12 PM, DM wrote:
> one of the way to find out schema name is like below
>
> select * from pg_tables where tablename like '%xyz%';
> schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
> ------------+-----------+------------+------------+------------+----------+-------------
> (0 rows)
>
> ~Deepak
>
Also a table/view could be in several schemas...
Jerry