查询所有表的索引相关信息视图(postgreSql 11.1)

 author: benson

date    : 2019.05.14 1449

CREATE OR REPLACE VIEW public."tableStructureIndexView" AS
 SELECT t.oid,
    n.nspname AS schema,
    t.relname AS "table",
    c.relname AS index,
    i.indisprimary AS "isPrimaryKey",
    i.indisunique AS "isUnique",
    a.attname AS fldname,
    pg_get_indexdef(i.indexrelid) AS ddl
   FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_index i ON i.indexrelid = c.oid
     JOIN pg_class t ON i.indrelid = t.oid
     JOIN pg_attribute a ON a.attrelid = t.oid AND (a.attnum = ANY (i.indkey::smallint[]))
  WHERE c.relkind = 'i'::"char" AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name])) AND pg_table_is_visible(c.oid) AND a.attnum > 0 AND a.attisdropped = false;

ALTER TABLE public."tableStructureIndexView"
    OWNER TO postgres;
COMMENT ON VIEW public."tableStructureIndexView"
    IS '所有表的索引视图';