Support for composite types

[green7ea]

I'm often writing queries that fetch complex nested data. I would love to have the nested data automatically typed by pgtyped. For example, using the books data, I could make the following query:

/* @name GetAuthorsWithBooks */
SELECT authors.first_name as first_name,
       authors.last_name as last_name,
       ARRAY(
         SELECT ROW(books.rank, books.name)
         FROM books
         WHERE books.author_id = authors.id
         ORDER BY books.rank
       ) AS books
FROM authors
ORDER BY authors.first_name, authors.last_name;

I would expected the typing to be equivalent to:

interface Result {
  first_name: string;
  last_name: string;
  books: {
    rank: number;
    name: string;
  }[];
}

Currently, pgtyped doesn't support this so I have to result to using JSON as the type for book and making a zod parser to validate it. Another option would be to using joins to get flat data and to do a group by in the javascript code. Both of these options are extra work and each brings its own downside.

Supporting ARRAY(SELECT ROW(...)) with pgtyped would be an amazing as it would simplify code and bring more type safety.

I would be open to doing this myself but I'm unfamiliar with the pgtyped project so I would need some general guidance as well as reviews.