Search Postgresql Archives

Re: I do not get the point of the information_schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <thiemo@xxxxxxxxxxxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux