Implement INFORMATION_SCHEMA.USER_DEFINED_TYPES and INFORMATION_SCHEMA.ATTRIBUTES

[lukaseder]

Is your feature request related to a problem? Please describe.

I would like to reverse engineer user-defined types from the INFORMATION_SCHEMA via the standard views:

• USER_DEFINED_TYPES
• ATTRIBUTES

Describe alternatives you've considered

I can probably work with queries against pg_type and pg_attribute as I've done before with the lack of ROUTINES support (see #104083):

select *
from pg_catalog.pg_type as t
join pg_catalog.pg_attribute as a on a.attrelid = t.typrelid
where t.typname = 't';

But for the future support of third party tooling, I still think these INFORMATION_SCHEMA views would be useful.

Jira issue: CRDB-31015

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_,ORM)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[lukaseder]

I don't recall what I may have done differently yesterday, but this query no longer seems to produce results:

select *
from pg_catalog.pg_type as t
join pg_catalog.pg_attribute as a on a.attrelid = t.typrelid
where t.typname = 't';

In fact:

create type u as (i int, j int);
select * from pg_attribute as a where attname in ('i', 'j');

Doesn't yield results on version 23.1.8. That means I won't be able to reverse engineer UDTs in any way, I think?

[lukaseder]

I've created another issue for the pg_class and pg_attribute population, as that is more important, I think:

• Populate pg_class and pg_attribute with composite types #109675

[rafiss]

We should be able to add these as views that match the PG definitions: https://github.com/postgres/postgres/blob/9b581c53418666205938311ef86047aa3c6b741f/src/backend/catalog/information_schema.sql#L2461

Similar to what we did for information_schema.routines: #105944

CREATE VIEW user_defined_types AS
    SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
           CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
           CAST(c.relname AS sql_identifier) AS user_defined_type_name,
           CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
           CAST('YES' AS yes_or_no) AS is_instantiable,
           CAST(null AS yes_or_no) AS is_final,
           CAST(null AS character_data) AS ordering_form,
           CAST(null AS character_data) AS ordering_category,
           CAST(null AS sql_identifier) AS ordering_routine_catalog,
           CAST(null AS sql_identifier) AS ordering_routine_schema,
           CAST(null AS sql_identifier) AS ordering_routine_name,
           CAST(null AS character_data) AS reference_type,
           CAST(null AS character_data) AS data_type,
           CAST(null AS cardinal_number) AS character_maximum_length,
           CAST(null AS cardinal_number) AS character_octet_length,
           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,
           CAST(null AS sql_identifier) AS collation_catalog,
           CAST(null AS sql_identifier) AS collation_schema,
           CAST(null AS sql_identifier) AS collation_name,
           CAST(null AS cardinal_number) AS numeric_precision,
           CAST(null AS cardinal_number) AS numeric_precision_radix,
           CAST(null AS cardinal_number) AS numeric_scale,
           CAST(null AS cardinal_number) AS datetime_precision,
           CAST(null AS character_data) AS interval_type,
           CAST(null AS cardinal_number) AS interval_precision,
           CAST(null AS sql_identifier) AS source_dtd_identifier,
           CAST(null AS sql_identifier) AS ref_dtd_identifier

    FROM pg_namespace n, pg_class c, pg_type t

    WHERE n.oid = c.relnamespace
          AND t.typrelid = c.oid
          AND c.relkind = 'c'
          AND (pg_has_role(t.typowner, 'USAGE')
               OR has_type_privilege(t.oid, 'USAGE'));

CREATE VIEW attributes AS
    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(nc.nspname AS sql_identifier) AS udt_schema,
           CAST(c.relname AS sql_identifier) AS udt_name,
           CAST(a.attname AS sql_identifier) AS attribute_name,
           CAST(a.attnum AS cardinal_number) AS ordinal_position,
           CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
           CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
             AS yes_or_no)
             AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.

           CAST(
             CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                  WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
                  ELSE 'USER-DEFINED' END
             AS character_data)
             AS data_type,

           CAST(
             _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS character_maximum_length,

           CAST(
             _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS character_octet_length,

           CAST(null AS sql_identifier) AS character_set_catalog,
           CAST(null AS sql_identifier) AS character_set_schema,
           CAST(null AS sql_identifier) AS character_set_name,

           CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
           CAST(nco.nspname AS sql_identifier) AS collation_schema,
           CAST(co.collname AS sql_identifier) AS collation_name,

           CAST(
             _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS numeric_precision,

           CAST(
             _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS numeric_precision_radix,

           CAST(
             _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS numeric_scale,

           CAST(
             _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS cardinal_number)
             AS datetime_precision,

           CAST(
             _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
             AS character_data)
             AS interval_type,
           CAST(null AS cardinal_number) AS interval_precision,

           CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
           CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
           CAST(t.typname AS sql_identifier) AS attribute_udt_name,

           CAST(null AS sql_identifier) AS scope_catalog,
           CAST(null AS sql_identifier) AS scope_schema,
           CAST(null AS sql_identifier) AS scope_name,

           CAST(null AS cardinal_number) AS maximum_cardinality,
           CAST(a.attnum AS sql_identifier) AS dtd_identifier,
           CAST('NO' AS yes_or_no) AS is_derived_reference_attribute

    FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
         JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
         JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
         LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
           ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')

    WHERE a.attnum > 0 AND NOT a.attisdropped
          AND c.relkind IN ('c')
          AND (pg_has_role(c.relowner, 'USAGE')
               OR has_type_privilege(c.reltype, 'USAGE'));