I do not get the point of the information_schema

[Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx>]

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

begin:vcard
fn:Thiemo Kellner
n:Kellner;Thiemo
adr:;;Landstr. 34;Weilheim-Bannholz;BW;79809;Deutschland
email;internet:thiemo@xxxxxxxxxxxxxxxxxxxx
tel;work:+49 1578 772 37 37
tel;cell:+41 78 947 36 21
note;quoted-printable:Auf Gelassene Pferde kann man bauen!=0D=0A=
	+49 (0)1578-772 37 37 (Mo, Di)=0D=0A=
	+41 (0)78 947 36 21 (Mi - Fr)=0D=0A=
	sip: thiemo.kellner@xxxxxxxxx=0D=0A=
	Skype: thiemo.kellner=0D=0A=
	http://www.gelassene-pferde.biz=0D=0A=
	Mitglied bei http://www.keep-it-natural.org=0D=0A=
	=C3=96ffentlicher PGP-Schl=C3=BCssel: http://pgp.mit.edu/pks/lookup?op=3D=
	get&search=3D0x8F70EFD2D972CBEF
x-mozilla-html:FALSE
url:http://www.gelassene-pferde.biz
version:2.1
end:vcard