types.sql.ts

import type { SQLQueryPropsWithSchemaFilterAndIdsFilter } from './common.js'

export const TYPES_SQL = (
  props: SQLQueryPropsWithSchemaFilterAndIdsFilter & {
    includeTableTypes?: boolean
    includeArrayTypes?: boolean
  }
) => /* SQL */ `
select
  t.oid::int8 as id,
  t.typname as name,
  n.nspname as schema,
  format_type (t.oid, null) as format,
  coalesce(t_enums.enums, '[]') as enums,
  coalesce(t_attributes.attributes, '[]') as attributes,
  obj_description (t.oid, 'pg_type') as comment,
  nullif(t.typrelid::int8, 0) as type_relation_id
from
  pg_type t
  left join pg_namespace n on n.oid = t.typnamespace
  left join (
    select
      enumtypid,
      jsonb_agg(enumlabel order by enumsortorder) as enums
    from
      pg_enum
    group by
      enumtypid
  ) as t_enums on t_enums.enumtypid = t.oid
  left join (
    select
      oid,
      jsonb_agg(
        jsonb_build_object('name', a.attname, 'type_id', a.atttypid::int8)
        order by a.attnum asc
      ) as attributes
    from
      pg_class c
      join pg_attribute a on a.attrelid = c.oid
    where
      c.relkind = 'c' and not a.attisdropped
    group by
      c.oid
  ) as t_attributes on t_attributes.oid = t.typrelid
  where
      (
        t.typrelid = 0
        or (
          select
            c.relkind ${props.includeTableTypes ? `in ('c', 'r', 'v', 'm')` : `= 'c'`}
          from
            pg_class c
          where
            c.oid = t.typrelid
        )
      )
      ${
        !props.includeArrayTypes
          ? `and not exists (
                 select
                 from
                   pg_type el
                 where
                   el.oid = t.typelem
                   and el.typarray = t.oid
               )`
          : ''
      }
      ${props.schemaFilter ? `and n.nspname ${props.schemaFilter}` : ''}
      ${props.idsFilter ? `and t.oid ${props.idsFilter}` : ''}
${props.limit ? `limit ${props.limit}` : ''}
${props.offset ? `offset ${props.offset}` : ''}
`