Syntax for expanding records in PostgreSQL

[p-himik]

In a query like

SELECT (v).*
FROM (SELECT v, row_number() OVER () rn FROM (VALUES (1, 2), (2, 3)) v(a, b)) v2
WHERE rn = 2;

you have to use (v).* because just v would return a record and v.* would lead to an error.

Currently, the way to do it is to use [:raw ["(" (sql/format-entity :v) ").*"]].

[seancorfield]

Something like this (name TBD):

user=> (sql/register-fn! :project (fn [_ [expr col]] (let [[sql & params] (sql/format-expr expr)] (into [(str "(" sql ")." (sql/format-entity col))] params))))
...
user=> (sql/format {:select [[[:project :v :*]]]})
["SELECT (v).*"]
user=> (sql/format {:select [[[:project :v :*]]]} {:quoted true})
["SELECT (\"v\").*"]
user=> (sql/format {:select [[[:project :v :a]]]} {:quoted true})
["SELECT (\"v\").\"a\""]
user=>

[seancorfield]

It turns out being able to dot-select from dynamic table or column names is useful so it makes more sense to split this in two:

• we already have :nest to wrap in (..)
• we could use :. (or '.) for the field selection since it sort of mirrors Clojure/Script

[seancorfield]

This has been pushed to develop for testing. Documentation still to do, including a migration note about qualify.

[seancorfield]

Currently, dot takes precisely two arguments: an expression and a column. qualify used to take an arbitrary number of table/column names (not an expression). Should dot be expanded to support multiple parts (i.e., expr followed by multiple subparts)?

[p-himik]

Personally, I have never seen any need to support an arbitrary amount of arguments. My own version of qualify supports just 2.

But it might be useful for people that often use schema.table.column.
Still can't imagine any need for 4-arity, but maybe some DBs do have that.

[seancorfield]

OK, I'll expand this to accept up to three arguments. And then document it.