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

Reply via email to