Add postgres array constructor for subqueries

[SimonSimCity]

I was scowarding through tickets on this repo and found a lot of things related to arrays, but I'm particularly interested in writing a typed query for postgres, where I can use an array constructor for a sub-query:

select *, array(select row(comments.*) from comments where posts.id = comments.post_id) from posts

The last example on https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS shows the SQL I want to write.

My goal is to conquer cartesian explosion and still keep writing just one query. As of now, I'm calling multiple queries (split-query approach).

Sidenote: #649 seems like a valid approach, but there you're still joining. I don't know what would be better performance wise. I found one mentioning performance testing and optimizing query plans (npgsql/efcore.pg#1691).. In fact, I'm quite new to this topic and want to write scalable code 😎