Support RECORD columns in PostgreSQL

[brianhv]

Example schema and query (fiddle):

CREATE TABLE location (
  id bigint primary key,
  name text
);

CREATE TABLE book (
  id bigint primary key,
  location_id bigint references location(id),
  name text
);

INSERT INTO location (id, name) VALUES (1, 'The nearby one');
INSERT INTO location (id, name) VALUES (2, 'The one across town');

INSERT INTO book (id, location_id, name) VALUES (1, 1, 'The Hitchhiker''s Guide to the Galaxy');
INSERT INTO book (id, location_id, name) VALUES (2, 1, 'The Restaurant at the End of the Universe');
INSERT INTO book (id, location_id, name) VALUES (3, 2, 'Life, the Universe and Everything');
INSERT INTO book (id, location_id, name) VALUES (4, 2, 'So Long, and Thanks for All the Fish');
INSERT INTO book (id, location_id, name) VALUES (5, 2, 'Mostly Harmless');

SELECT location.id, location.name, books_at_location.bal
FROM location
LEFT JOIN LATERAL (
    SELECT ARRAY_AGG(ROW(book.id, book.name)) bal
    FROM book
    WHERE location.id = book.location_id
) books_at_location ON true;

id	name	bal
1	The nearby one	{"(1,"The Hitchhiker's Guide to the Galaxy")","(2,"The Restaurant at the End of the Universe")"}
2	The one across town	{"(3,"Life, the Universe and Everything")","(4,"So Long, and Thanks for All the Fish")","(5,"Mostly Harmless")"}

Currently sqlx gives the error message
error: unsupported type RECORD[] of column #3 ("bal")

[abonander]

I don't see a way we could make this work with the macros because Postgres doesn't tell us the expected subtypes of a RECORD column; from my understanding, it could be an entirely different set of columns per row.

If you converted the bal column to a custom typedef, that would work with #148.

[abonander]

In the meantime you could try aggregating the subquery to a JSON array instead, using jsonb_agg() instead of array_agg.

[mehcode]

Sadly this isn't possible because we don't receive enough information at compile time.