You might find Aquameta's meta module helpful, it reimplements information_schema in a more normalized layout, as updatable views:
http://blog.aquameta.com/2015/08/29/intro-meta/ https://github.com/aquametalabs/aquameta/tree/master/core/000-meta Best, Eric On Mon, Feb 12, 2018 at 2:02 PM Thiemo Kellner <thi...@gelassene-pferde.biz> wrote: > 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 > -- -- Eric Hanson CEO, Aquameta 503-929-1073
