Найти все типы компонентов составного типа, используя WITH RECURSIVE

Question

Учитывая typname составного типа, как мне найти тип oids из всех типов компонентов рекурсивно?

Пример:

CREATE TYPE t_station AS (x INT,
                          y INT,
                          label VARCHAR);

CREATE TYPE t_address AS (city VARCHAR,
                          street VARCHAR,
                          no INT,
                          stations t_station[]);

CREATE TYPE t_employee AS (name VARCHAR,
                           age INT,
                           coins INT[],
                           notes VARCHAR,
                           address t_address);

Я могу получить тип oids из членов t_employee:

SELECT
   t.typname, t.oid, a.attname, a.atttypid
FROM
   pg_attribute a INNER JOIN pg_type t ON a.attrelid = t.typrelid
   AND t.typname = 't_employee'

Но мне нужно повторить то, что, я думаю, можно сделать, используя WITH RECURSIVE:

WITH RECURSIVE allattrs(typname, oid, attname, atttypid) AS (
  select t.typname, t.oid, a.attname, a.atttypid from pg_attribute a inner join pg_type t on a.attrelid = t.typrelid and t.typname = 't_employee'
  union all
  select z.* from
  (select t.typname, t.oid, a.attname, a.atttypid from pg_attribute a inner join pg_type t on a.attrelid = t.typrelid) z,
  allattrs y where y.atttypid = z.oid
)
SELECT * FROM allattrs limit 100
;

Но это не находит внутренний массив t_station составной тип.

Answer 1

Типы массивов разрывают простую цепочку, которой вы следуете. В случае типа массива вы должны разрешить pg_type.typelem чтобы добраться до базового типа.

WITH RECURSIVE cte(typname, type_oid, attname, atttypid, typelem) AS (
   SELECT t.typname, t.oid, a.attname, a.atttypid, t.typelem
   FROM   pg_type t
   LEFT   JOIN pg_attribute a ON  a.attrelid = t.typrelid
                              AND a.attnum > 0
                              AND NOT a.attisdropped
   WHERE  t.typrelid = 't_employee'::regclass

   UNION ALL
   SELECT t.typname, t.oid
         ,COALESCE(a.attname, t.typelem::regtype::text)
         ,COALESCE(a.atttypid, t.typelem), t.typelem
   FROM   cte c
   JOIN   pg_type t ON t.oid = c.atttypid AND (t.typtype = 'c' OR t.typelem > 0)
   LEFT   JOIN pg_attribute a ON  a.attrelid = t.typrelid
                              AND a.attnum > 0
                              AND NOT a.attisdropped
   )
SELECT typname, type_oid, attname, atttypid
FROM   cte
WHERE  typelem = 0  -- filter out rows for array types

Если вы хотите включить в результат дополнительные строки для типов массивов, удалите окончательное условие WHERE.

Это условие JOIN следует только за составными типами или массивами:

AND (t.typtype = 'c' OR t.typelem > 0)

Я также добавил условия для исключения системных и мертвых столбцов:

AND a.attnum > 0
AND NOT a.attisdropped

Подробности о каталогах таблиц в руководстве.