Support for converting DuckDB STRUCT type to Postgres composite ROW type

[JelteF]

Description

DuckDB has anonymous STRUCT types, with named fields. Postgres doesn't have an equivalent type. The best we can probably do is use the ROW type. This is anonymous compound type, but the fields are not named.

Once supported we can un-exclude the STRUCT types from this test:

pg_duckdb/test/regression/expected/test_all_types.out

Lines 15 to 16 in 02ebeae

	struct,
	struct_of_arrays,

[destrex271]

@JelteF picking this up

[destrex271]

is it possible to consider using jsonb for the struct type? There are some similarities in how operations are performed on them while writing sql.

[JelteF]

Hmm, yeah I think this one is actually more difficult than I thought it was. We'll probably want to create a new Postgres type. This type should be very similar to the duckdb.row type (which was introduced in pg_duckdb 0.3). The main difference is that it should not automatically "star expand". Other than that it can behave the same duckdb.row, i.e. implement the subscripting (aka indexing) operator which returns a duckdb.unresolved_type.

If a query then actually outputs a duckdb.row, only then should a STRUCT type be mapped to postgres ROW type.

[destrex271]

Cool, I'll get started with this then

[destrex271]

Hi @JelteF , this might take me some time to complete, trying out various methods for this