Find out whether a view's column is indexed?

Поиск
Список
Период
Сортировка
От Martin Schäfer
Тема Find out whether a view's column is indexed?
Дата
Msg-id 37936EEC582B394A9E1AA951991A551604C3BF@dev001_pdc.Dev.cadcorp.net
обсуждение исходный текст
Ответы Re: Find out whether a view's column is indexed?
Список pgsql-sql
Is there any way to find out whether a column that's used in a view is indexed?
The following query:

SELECT ic.relname AS index_name
FROM pg_class bc,    pg_class ic,    pg_index i,    pg_attribute a,    pg_opclass oc,    pg_namespace n
WHERE i.indrelid = bc.oid AND     i.indexrelid = ic.oid AND     i.indkey[0] = a.attnum AND     i.indclass[0] = oc.oid
AND    a.attrelid = bc.oid AND     oc.opcname = 'gist_geometry_ops' AND     n.oid = bc.relnamespace AND     bc.relkind
~'[rv]' AND     ic.relkind = 'i' AND     n.nspname = 'foo' AND     bc.relname = 'bar' AND     a.attname = 'foobar'; 

lets me find out whether a table column is indexed, but it doesn't work for views. Is there anything that can be done
forviews? At least for simple views of the kind 'CREATE VIEW v AS SELECT a,b,c FROM t'? 

Can anybody help?
Martin

PS: as you can see from the query I'm using the PostGIS extension, and I'm only interested in spatial indices on
geometrycolumns. 


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Getting FK relationships from information_schema
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Find out whether a view's column is indexed?