I do not get the point of the information_schema
От | Thiemo Kellner |
---|---|
Тема | I do not get the point of the information_schema |
Дата | |
Msg-id | 303d3d2c-ef0b-af0a-e4d1-4b17a029fc0f@gelassene-pferde.biz обсуждение исходный текст |
Ответы |
Re: I do not get the point of the information_schema
Re: I do not get the point of the information_schema Re: I do not get the point of the information_schema |
Список | pgsql-general |
I try to implement SCD2 on trigger level and try to generated needed code on the fly. Therefore I need to read data about the objects in the database. So far so good. I know of the information_schema and the pg_catalog. The documentation for the information_schema states that it 'is defined in the SQL standard and can therefore be expected to be portable and remain stable'. I can think of a sensible meaning of portable. One cannot port it to MariaDB, can one? Maybe different PostreSQL version but then a one fits all implementation would mean only parts of the catalogue that never ever change can be exposed by the information_schema. Coming from Oracle I consider the information_schema the analogy to Oracles data dictionary views giving a stable interface on the database metadata hiding catalogue structure changes. But I dearly miss some information therein. I created following query to get the index columns of an index. I fear breakage when not run on the specific version I developed it against. Is there a more elegant way by the information_schema? with INDEX_COLUMN_VECTOR as( select i.indkey from pg_catalog.pg_index i inner join pg_catalog.pg_class c on i.indexrelid = c.oid where c.relname = 'idiom_hist' ), COLUMNS as( select a.attname, a.attnum from pg_catalog.pg_attribute a inner join pg_catalog.pg_class c on a.attrelid = c.oid where c.relname = 'idiom' ) select c.attname from COLUMNS c inner join INDEX_COLUMN_VECTOR v on c.attnum = any(v.indkey) order by c.attnum asc; An other simpler case. select indexname from pg_catalog.pg_indexes where schemaname = 'act' and tablename = i_table_name and indexname = i_table_name || '_hist'; -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC
Вложения
В списке pgsql-general по дате отправления: