We're wanting to use SQLDelight with a PSQL-compat...

# squarelibraries

[phldavies]

We're wanting to use SQLDelight with a PSQL-compatible database that supports bitemporal queries via the SQL:2011 <table> FOR <SYSTEM_TIME|VALID_TIME> AS OF <TIMESTAMP> style syntax - what's the best approach to enabling this within SQLDelight? Ideally adding support to the PostgresDialect would be simplest I believe, however I know that Postgres doesn't directly support this syntax so it doesn't feel entirely correct to add it there. Currently we're patching the generated code where required but obviously that's rather a fragile approach and is only workable while we're not actively parameterising the temporal bounds of the query.

[hfhbd]

Honestly I never used it. Is this a built in stuff or do you need to enable/install something? If it’s built in, I am okay with having it in sqldelight, otherwise it’s way better to write a sqldelight driver module, like the SQLite json module, and we link them in the docs. This option also allows you to develop on your speed not waiting for a PR review/new release of sqldelight.

[phldavies]

It's not a postgres extension but a different database that provides a PGWire compatible interface (and extending the PSQL syntax)

[phldavies]

for reference, the system in question is XTDB v2: https://docs.xtdb.com/reference/main/sql/queries.html

[hfhbd]

Okay, so if you only need to add grammar without changing the existing one, use a module.

[phldavies]

Would I be right in thinking the only guidance here is the existing example modules (i.e. sqllite/json, and third party dialects?)

[hfhbd]

Yeah, there is no real documentation but if you have any questions feel free to ask.