Reading record types (e.g. composite types or anonymous records from ROW constructor)

[SanjayVas]

Feature Request

Is your feature request related to a problem? Please describe

When dealing with one-to-many relationships, it's often convenient to do a single query where the "many" side is represented as an array of records. For example, supposing you have a library DB with Authors and Books. For simplicity, let's assume there's a one-to-many relationship between Authors and Books (a Book has exactly one Author, and an Author has many Books).

Sample query:

SELECT
  AuthorId,
  Name,
  ARRAY(SELECT ROW(BookId, Title) FROM Books WHERE Books.AuthorId = Authors.AuthorId) AS BookArr
FROM Authors

Ideally there would be an easy way to parse the value of each item in the BookArr "column".

Describe the solution you'd like

Something analogous to how the Google Cloud Spanner Java client library handles this. That library has a Struct type that is analogous to R2DBC's Reader. For cases like this, the mapped type for BookArr should be an array of Reader, where you can use each Reader to read the values from the anonymous record just like you would a normal row.

Note that the closest Google Standard SQL syntax for the above query would be

SELECT
  AuthorId,
  Name,
  ARRAY(SELECT AS STRUCT BookId, Title FROM Books WHERE Books.AuthorId = Authors.AuthorId) AS BookArr
FROM Authors

Describe alternatives you've considered

Right now, what we're doing is completely separate queries instead of the array column (inefficient). We could do joins instead, but that gives a messy output structure. Another option is using a different structured type that is handled by the library, such as JSON. Of course, that's more manual conversion work.

A simpler solution would be a codec that parses to a simple flat collection, but the benefit of using Reader is getting decoding for any type that has a registered codec.

Teachability, Documentation, Adoption, Migration Strategy

The record type is OID 2249. I'm not sure if this can be done via the Codec mechanism or how hard it would be to implement a version of Reader that can wrap a nested column. Another catch might be that the text format might drop the name information, meaning you can only read by index rather than name.

[mp911de]

We provide an extension SPI that can be used to register custom codecs. We also have a custom codec feature to map Java enums to Postgres enums. Having two pieces of information: the OID and the Java type is sufficient to read and write values. Without a Java type, the OID itself doesn't seem sufficient.

I suggest exploring such an approach in your own codebase to understand how something like that could work. Since Java record components are in order, we do not strictly require names in the output.

[SanjayVas]

That's basically the alternative I listed: a codec that maps to a simple flat collection.

The goal here is to have something generic that works for arbitrary record types, where any field within the record could be any type. As a result, accessing any such field would need to go through the same codec resolution process as accessing a top-level value from a row. I'm not sure this is feasible through the Codec interface, at least at first glance. Hence why I think it may need to be implemented within r2dbc-postgresql.

[mp911de]

Hence why I think it may need to be implemented within r2dbc-postgresql.

Not opposed to inclusion. It is our normal approach to related functionality to verify features somewhere first before we merge features into a project/production version where we commit to maintenance.