[SQL] How can I traverse nested data (STRUCTs) in a DuckDB / SQL data dictionary?

In DuckDB, how can I traverse a nested structure (STRUCT), using either INFORMATION_SCHEMA or the duckdb_* schema functions? I would also like to recognize an array type. It would be nice if the solution were portable to Postgresql.

For example, the table

CREATE TABLE t(x BIGINT, y STRUCT(a BIGINT, b TEXT), z TEXT[]);


would generate a data struture that looks like this.

x BIGINT
y STRUCT
y.a BIGINT
y.b TEXT
z ARRAY-TEXT


Context: writing some schema tracking tools.

Continue reading...