Data pipeline or PL support

[beikov]

PostgreSQL and DB2 support building "data pipelines" in SQL i.e. have one query which acts on the result of DML statements that are also embedded in the query.
Other DBs usually support executing inline/anonymous procedures which can be used to emulate this. As a last resort, we could also just execute individual statements and push results to temporary tables or use the VALUES clause mechanism to propagate the values further.

The PostgreSQL syntax uses the RETURNING clause like this:

WITH inserted_result AS (
  insert into some_table ...
  returning id
),
queued_mails AS (
  insert into some_other_table ...
  returning mail_id
)
select 
  (select count(*) from inserted_result),
  (select count(*) from queued_mails)

I don't know if it's worth adding support for DML in CTEs explicitly or if it might be better to add a HQL based procedural language that we can translate to this SQL construct.

Having the possibility on the SQL AST to model procedural logic is definitely interesting as that will open the door for some JDBC driver pipelining optimizations.