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

<<attachment: thiemo.vcf>>

Reply via email to