Support for PostgreSQL DISTINCT ON syntax

[lnhrdt]

SQLDelight Version

2.0.0

SQLDelight Dialect

PostgreSQL

Describe the Bug

The PostgreSQL DISTINCT ON syntax does not seem to be supported. Queries like this do not compile in SQLDelight:

test:
SELECT DISTINCT ON (name) *
FROM person
ORDER BY person.created_at DESC;

Compilation errors appear on the name of the query:

'{' expected, got ':'

https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT

Stacktrace

No response

[griffio]

😎 I will have a look and do a PR to see what happens

It was on the list 📜 #3944 (reply in thread)

This syntax should also validate "SELECT DISTINCT ON expressions must match initial ORDER BY expressions", as though that test example was provided for context, if implemented it shouldn't compile 🙅 , the test query should be

SELECT DISTINCT ON (name) *
FROM person
ORDER BY person.name, person.created_at DESC;

I also recently created another discussion about the current schema migration support (if you have any requirements 🛎️ ) - #4572

[lnhrdt]

Aha the list in #3944 (reply in thread)! I'd forgotten about that, thank you. Over the last 2 months I've definitely run into a few of those "not supported syntax" items so I just shared some more thoughts in there.

Fascinating what you're discussing in #4572. I feel out of my depth discussing how to better support vendor extensions in sql-psi but I applaud and appreciate your work!