Hello all. PostgreSQL has for a while the capability to do crud (create/read/update/delete) operations from/to a json hierarchy. Writing this sql is both painful and verbose but possible. This capability has been increasingly incorporated in the postgres ecosystem to build various dsl -> sql tools. One good example is hasura from a previous post that converts graphql to sql. The assumption is to be able to perform one single sql query with json as input/output. I've been playing around with the same for calcite and reached the following conclusion: Create (select) works fine with the available json functions and what is missing isn't terribly hard to add. Insert/update on the other doesn't play too well due to the following missing sql features: 1) Returning fields on insert: link.
| | | | | | | | | | | 6.4. Returning Data from Modified Rows 6.4. Returning Data from Modified Rows Sometimes it is useful to obtain data from modified rows while they are b... | | | 2) modifiable CTE: link. I'm wondering how hard these features would be to implement in calcite? They seem to me to be anything but trivial. Is this something an average calcite tinkerer like myself could do? In 1), returning fields from inserts on a jdbc data source that does not support it would require 2 queries and some business logic. That would require additions to all sql dialect?In 2), I assume quite a bit or rewrite of the calcite engine plus the famous spool operator? Of course I could write this in an UDF (User Defined Function) but it turns out to be same effort as much of an ORM (object relational mapping) and thus better to have in the core. Any thought? /Martin
