watford-ep commented on PR #18342:
URL: https://github.com/apache/datafusion/pull/18342#issuecomment-3458186546
I've also confirmed this fixes my issue with the following query issued by
npgsql:
```sql
SELECT ns.nspname, t.oid, t.typname, t.typtype, t.typnotnull, t.elemtypoid
FROM (
-- Arrays have typtype=b - this subquery identifies them by their
typreceive and converts their typtype to a
-- We first do this for the type (innerest-most subquery), and then
for its element type
-- This also returns the array element, range subtype and domain
base type as elemtypoid
SELECT
typ.oid, typ.typnamespace, typ.typname, typ.typtype,
typ.typrelid, typ.typnotnull, typ.relkind,
elemtyp.oid AS elemtypoid, elemtyp.typname AS elemtypname,
elemcls.relkind AS elemrelkind,
CASE WHEN elemproc.proname='array_recv' THEN 'a' ELSE
elemtyp.typtype END AS elemtyptype
FROM (
SELECT typ.oid, typnamespace, typname, typrelid, typnotnull,
relkind, typelem AS elemoid,
CASE WHEN proc.proname='array_recv' THEN 'a' ELSE
typ.typtype END AS typtype,
CASE
WHEN proc.proname='array_recv' THEN typ.typelem
WHEN typ.typtype='r' THEN rngsubtype
WHEN typ.typtype='m' THEN (SELECT rngtypid FROM pg_range
WHERE rngmultitypid = typ.oid)
WHEN typ.typtype='d' THEN typ.typbasetype
END AS elemtypoid
FROM pg_type AS typ
LEFT JOIN pg_class AS cls ON (cls.oid = typ.typrelid)
LEFT JOIN pg_proc AS proc ON proc.oid = typ.typreceive
LEFT JOIN pg_range ON (pg_range.rngtypid = typ.oid)
) AS typ
LEFT JOIN pg_type AS elemtyp ON elemtyp.oid = elemtypoid
LEFT JOIN pg_class AS elemcls ON (elemcls.oid = elemtyp.typrelid)
LEFT JOIN pg_proc AS elemproc ON elemproc.oid = elemtyp.typreceive
) AS t
JOIN pg_namespace AS ns ON (ns.oid = typnamespace)
WHERE
typtype IN ('b', 'r', 'm', 'e', 'd') OR -- Base, range, multirange,
enum, domain
(typtype = 'c' AND relkind='c') OR -- User-defined free-standing
composites (not table composites) by default
(typtype = 'p' AND typname IN ('record', 'void')) OR -- Some special
supported pseudo-types
(typtype = 'a' AND ( -- Array of...
elemtyptype IN ('b', 'r', 'm', 'e', 'd') OR -- Array of base,
range, multirange, enum, domain
(elemtyptype = 'p' AND elemtypname IN ('record', 'void')) OR --
Arrays of special supported pseudo-types
(elemtyptype = 'c' AND elemrelkind='c') -- Array of user-defined
free-standing composites (not table composites) by default
))
ORDER BY CASE
WHEN typtype IN ('b', 'e', 'p') THEN 0 -- First base
types, enums, pseudo-types
WHEN typtype = 'r' THEN 1 -- Ranges after
WHEN typtype = 'm' THEN 2 -- Multiranges
after
WHEN typtype = 'c' THEN 3 -- Composites
after
WHEN typtype = 'd' AND elemtyptype <> 'a' THEN 4 -- Domains over
non-arrays after
WHEN typtype = 'a' THEN 5 -- Arrays after
WHEN typtype = 'd' AND elemtyptype = 'a' THEN 6 -- Domains over
arrays last
END
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
