scripts/collector/postgres/sql/schema_objects.sql [16:193]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
with all_tables as (
  select distinct c.oid as object_id,
    'TABLE' as object_category,
    case
      when c.relkind = 'r' then 'TABLE'
      when c.relkind = 'S' then 'SEQUENCE'
      when c.relkind = 'f' then 'FOREIGN_TABLE'
      when c.relkind = 'p' then 'PARTITIONED_TABLE'
      when c.relkind = 'c' then 'COMPOSITE_TYPE'
      when c.relkind = 't' then 'TOAST_TABLE'
      else 'UNCATEGORIZED_TABLE'
    end as object_type,
    ns.nspname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_class c
    join pg_catalog.pg_namespace as ns on (c.relnamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
    and c.relkind = ANY (ARRAY ['r', 'p', 'S', 'f', 'c','t'])
),
all_views as (
  select distinct c.oid as object_id,
    'VIEW' as object_category,
    case
      when c.relkind = 'v' then 'VIEW'
      when c.relkind = 'm' then 'MATERIALIZED_VIEW'
      else 'UNCATEGORIZED_VIEW'
    end as object_type,
    ns.nspname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_class c
    join pg_catalog.pg_namespace as ns on (c.relnamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
    and c.relkind = ANY (ARRAY [ 'v', 'm'])
),
all_indexes as (
  select distinct i.indexrelid as object_id,
    'INDEX' as object_category,
    case
      when c.relkind = 'I'
      and c.relname !~ '^pg_toast' then 'PARTITIONED_INDEX'
      when c.relkind = 'I'
      and c.relname ~ '^pg_toast' then 'TOAST_PARTITIONED_INDEX'
      when c.relkind = 'i'
      and c.relname !~ '^pg_toast' then 'INDEX'
      when c.relkind = 'i'
      and c.relname ~ '^pg_toast' then 'TOAST_INDEX'
      else 'UNCATEGORIZED_INDEX'
    end as object_type,
    sut.relname as table_name,
    sut.schemaname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_index i
    join pg_stat_user_tables sut on (i.indrelid = sut.relid)
    join pg_class c on (i.indexrelid = c.oid)
),
all_constraints as (
  select distinct con.oid as object_id,
    'CONSTRAINT' as object_category,
    case
      when con.contype = 'c' then 'CHECK_CONSTRAINT'
      when con.contype = 'f' then 'FOREIGN_KEY_CONSTRAINT'
      when con.contype = 'p' then 'PRIMARY_KEY_CONSTRAINT'
      when con.contype = 'u' then 'UNIQUE_CONSTRAINT'
      when con.contype = 't' then 'CONSTRAINT_TRIGGER'
      when con.contype = 'x' then 'EXCLUSION_CONSTRAINT'
      else 'UNCATEGORIZED_CONSTRAINT'
    end as object_type,
    ns.nspname as object_schema,
    con.conname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_constraint con
    join pg_class as c on con.conrelid = c.oid
    join pg_catalog.pg_namespace as ns on (con.connamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
),
all_triggers as (
  select distinct t.tgrelid as object_id,
    'TRIGGER' as object_category,
    case
      t.tgtype::integer & 66
      when 2 then 'BEFORE'
      when 64 then 'INSTEAD_OF'
      else 'AFTER'
    end || '_' || case
      t.tgtype::integer & cast(28 as int2)
      when 16 then 'UPDATE'
      when 8 then 'DELETE'
      when 4 then 'INSERT'
      when 20 then 'INSERT_UPDATE'
      when 28 then 'INSERT_UPDATE_DELETE'
      when 24 then 'UPDATE_DELETE'
      when 12 then 'INSERT_DELETE'
    end || '_' || 'TRIGGER' as object_type,
    ns.nspname as object_schema,
    t.tgname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_trigger t
    join pg_class c on t.tgrelid = c.oid
    join pg_namespace ns on ns.oid = c.relnamespace
    /* exclude triggers generated from constraints */
  where t.tgrelid not in (
      select conrelid
      from pg_constraint
    )
),
all_procedures as (
  select distinct p.oid as object_id,
    'SOURCE_CODE' as object_category,
    ns.nspname as object_schema,
    case
      when p.prokind = 'f' then 'FUNCTION'
      when p.prokind = 'p' then 'PROCEDURE'
      when p.prokind = 'a' then 'AGGREGATE_FUNCTION'
      when p.prokind = 'w' then 'WINDOW_FUNCTION'
      else 'UNCATEGORIZED_PROCEDURE'
    end as object_type,
    p.proname as object_name,
    pg_get_userbyid(p.proowner) as object_owner
  from pg_proc p
    left join pg_namespace ns on ns.oid = p.pronamespace
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
),
src as (
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_tables a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_views a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_indexes a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_procedures a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_constraints a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_triggers a
)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



src/dma/collector/sql/sources/postgres/collection-schema_objects.sql [17:194]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
with all_tables as (
  select distinct c.oid as object_id,
    'TABLE' as object_category,
    case
      when c.relkind = 'r' then 'TABLE'
      when c.relkind = 'S' then 'SEQUENCE'
      when c.relkind = 'f' then 'FOREIGN_TABLE'
      when c.relkind = 'p' then 'PARTITIONED_TABLE'
      when c.relkind = 'c' then 'COMPOSITE_TYPE'
      when c.relkind = 't' then 'TOAST_TABLE'
      else 'UNCATEGORIZED_TABLE'
    end as object_type,
    ns.nspname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_class c
    join pg_catalog.pg_namespace as ns on (c.relnamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
    and c.relkind = ANY (ARRAY ['r', 'p', 'S', 'f', 'c','t'])
),
all_views as (
  select distinct c.oid as object_id,
    'VIEW' as object_category,
    case
      when c.relkind = 'v' then 'VIEW'
      when c.relkind = 'm' then 'MATERIALIZED_VIEW'
      else 'UNCATEGORIZED_VIEW'
    end as object_type,
    ns.nspname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_class c
    join pg_catalog.pg_namespace as ns on (c.relnamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
    and c.relkind = ANY (ARRAY [ 'v', 'm'])
),
all_indexes as (
  select distinct i.indexrelid as object_id,
    'INDEX' as object_category,
    case
      when c.relkind = 'I'
      and c.relname !~ '^pg_toast' then 'PARTITIONED_INDEX'
      when c.relkind = 'I'
      and c.relname ~ '^pg_toast' then 'TOAST_PARTITIONED_INDEX'
      when c.relkind = 'i'
      and c.relname !~ '^pg_toast' then 'INDEX'
      when c.relkind = 'i'
      and c.relname ~ '^pg_toast' then 'TOAST_INDEX'
      else 'UNCATEGORIZED_INDEX'
    end as object_type,
    sut.relname as table_name,
    sut.schemaname as object_schema,
    c.relname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_index i
    join pg_stat_user_tables sut on (i.indrelid = sut.relid)
    join pg_class c on (i.indexrelid = c.oid)
),
all_constraints as (
  select distinct con.oid as object_id,
    'CONSTRAINT' as object_category,
    case
      when con.contype = 'c' then 'CHECK_CONSTRAINT'
      when con.contype = 'f' then 'FOREIGN_KEY_CONSTRAINT'
      when con.contype = 'p' then 'PRIMARY_KEY_CONSTRAINT'
      when con.contype = 'u' then 'UNIQUE_CONSTRAINT'
      when con.contype = 't' then 'CONSTRAINT_TRIGGER'
      when con.contype = 'x' then 'EXCLUSION_CONSTRAINT'
      else 'UNCATEGORIZED_CONSTRAINT'
    end as object_type,
    ns.nspname as object_schema,
    con.conname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_constraint con
    join pg_class as c on con.conrelid = c.oid
    join pg_catalog.pg_namespace as ns on (con.connamespace = ns.oid)
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
),
all_triggers as (
  select distinct t.tgrelid as object_id,
    'TRIGGER' as object_category,
    case
      t.tgtype::integer & 66
      when 2 then 'BEFORE'
      when 64 then 'INSTEAD_OF'
      else 'AFTER'
    end || '_' || case
      t.tgtype::integer & cast(28 as int2)
      when 16 then 'UPDATE'
      when 8 then 'DELETE'
      when 4 then 'INSERT'
      when 20 then 'INSERT_UPDATE'
      when 28 then 'INSERT_UPDATE_DELETE'
      when 24 then 'UPDATE_DELETE'
      when 12 then 'INSERT_DELETE'
    end || '_' || 'TRIGGER' as object_type,
    ns.nspname as object_schema,
    t.tgname as object_name,
    pg_get_userbyid(c.relowner) as object_owner
  from pg_trigger t
    join pg_class c on t.tgrelid = c.oid
    join pg_namespace ns on ns.oid = c.relnamespace
    /* exclude triggers generated from constraints */
  where t.tgrelid not in (
      select conrelid
      from pg_constraint
    )
),
all_procedures as (
  select distinct p.oid as object_id,
    'SOURCE_CODE' as object_category,
    ns.nspname as object_schema,
    case
      when p.prokind = 'f' then 'FUNCTION'
      when p.prokind = 'p' then 'PROCEDURE'
      when p.prokind = 'a' then 'AGGREGATE_FUNCTION'
      when p.prokind = 'w' then 'WINDOW_FUNCTION'
      else 'UNCATEGORIZED_PROCEDURE'
    end as object_type,
    p.proname as object_name,
    pg_get_userbyid(p.proowner) as object_owner
  from pg_proc p
    left join pg_namespace ns on ns.oid = p.pronamespace
  where ns.nspname <> all (array ['pg_catalog', 'information_schema'])
    and ns.nspname !~ '^pg_toast'
),
src as (
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_tables a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_views a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_indexes a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_procedures a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_constraints a
  union all
  select a.object_owner,
    a.object_category,
    a.object_type,
    a.object_schema,
    a.object_name,
    a.object_id
  from all_triggers a
)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



