Allow use of DML with RETURNING clause in SELECT

[mrotteveel]

It would be useful to allow access to DML with RETURNING clauses in SELECT. The SQL standard itself provides something called a "table function derived table " (see 7.6 <table reference> in SQL:2016-2), allowing you to access a table-valued function as a table. Given DML with RETURNING could be considered a table-valued function, this syntax could be useful to provide access to DML in select.

Note that SQL standard also has something called a "data change delta table" (see also section 7.6 in SQL:2016-2), which is intended to do the same (providing access to the changes of a DML statement in a SELECT), but it comes with additional clauses which makes no sense for us, but essentially below proposal is a combination of "table function derived table " and "data change delta table".

My proposal is the follows:

<data-change-table> ::= TABLE '(' <modifying-dml> ')' [AS] <alias> [(<derived-column-list>)]
<modifying-dml> :: = { <insert> | <update-searched> | <update-or-insert> | <delete-searched> | <merge> }  

<table-primary> ::= { ..existing options.. | <data-change-table> }

With the added requirement that the modifying-dml must have a RETURNING clause. The data-change-table would be similar to a derived-table.

It should be possible to use columns of joined tables as parameters, similar to using selectable stored procedures, but we may need to consider if that should require explicit use of LATERAL or not.

We could also consider extending the same treatment to EXECUTE BLOCK with RETURNS, and maybe EXECUTE PROCEDURE, but that might be something for later.

Alternatives:

• The requirement that a statement must have RETURNING could also be replaced with providing an empty table stream if the RETURNING clause is absent. This might not seem very useful, but will allow calling a DML statement from within a select, even if you're not interested in the result.

[asfernandes]

I don't understand why you want to SELECT have RETURNING.
Isn't RETURNING the same as the SELECT value list?
Please give some usage examples.

[mrotteveel]

I'm not talking about adding a RETURNING to SELECT, I'm talking about allowing you to use an INSERT, UPDATE, UPDATE OR INSERT, DELETE or MERGE with a RETURNING clause as a form of derived-table in a select statement.

So for example:

select i.id, o.some_name, i.some_value
from table (
  insert into sometable (id, some_id, some_value) values (1, 513, 'abc')
  returning id, some_id, some_value
) i
inner join othertable o
  on o.some_id = i.some_id

[mrotteveel]

A bit more complicated example for 'archiving' data to another table (using LATERAL here, but we may not need that)

select o.id as original_id, n.id as archive_id, l.id updated_link_id
from table (
  delete from original_table where expiry_date < current_date 
  returning id, some_data_1, some_data_2, some_data_3
) o,
lateral table (
  insert into archive_table (some_data_1, some_data_2, some_data_3) 
    values (o.some_data_1, o.some_data_2, o.some_data_3)
  returning id
) n,
lateral table (
  update some_linked_table set archive_id = n.id where original_id = o.id
  return id
) l

[tonne1]

plus 1: actually just tested this yesterday to see if it was possible.
Could also be a workaround for #4722

The more I think about it - this would be a powerful new feature, reduce the need for stored procedures and EBs and allow to write readable and compact statements with minimal overhead and code which were impossible til now - and, bonus, transparent to clients.

Checked Postgres, Oracle and MariaDB, but they do not or only partly offer something in that direction, and if, not as elegant and straightforward.

[mrotteveel]

PostgreSQL actually does support something like this:

with a as (
   insert into example(val) values ('abc') returning id, val
)
select * 
from a
left join (values ('abc', 'x')) x (lookup, val2)
  on a.val = x.lookup

dbfiddle: https://dbfiddle.uk/_ZD5GaEw

See also Data-Modifying Statements in WITH

Though my suggestion with allowing it to use as lateral tables to do additional work based on previous tables is not supported in PostgreSQL as far as I can tell.

[sim1984]

I am categorically against such constructions. Do not forget that the optimizer can choose any order of joining tables, which makes the result of such queries unpredictable.

[mrotteveel]

BTW: Thinking of this now, this could also be used as a solution for using multirow RETURNING in PSQL:

for select * from table (insert into example (x, y, z) select x, y, x from othertable returning id, x, y, z, calculated_column) into ...

[mrotteveel]

I am categorically against such constructions. Do not forget that the optimizer can choose any order of joining tables, which makes the result of such queries unpredictable.

1. You can change the optimizer
2. Lateral tables establish a dependency order based on the correlated values, so the order is already deterministic
3. For uncorrelated modifying DML statements, you can execute the DML once and use the resulting table as a stable record source (this is basically what PostgreSQL does with its WITH solution).
4. Maybe the lateral solution for correlated modifying DML is a bridge too far right now, but as a first step we could support uncorrelated DML this way, and refine/improve it later to allow correlated DML

[sim1984]

By the way, it would be possible to use internal local temporary tables, which are now used for returning to implement queries like 'values (1,2), (3, 4)...'

[aafemt]

Do not forget that the optimizer can choose any order of joining tables

Not quite so. Returning table is not indexed in any way so it hardly can be second stream. And even if plan is JOIN (A NATURAL, B NATURAL) the second stream is fully scanned.

[mrotteveel]

By the way, it would be possible to use internal local temporary tables, which are now used for returning to implement queries like 'values (1,2), (3, 4)...'

That is a table-value constructor and is unrelated to this proposal.

[sim1984]

Don't think in the past. Even for an unindexed table join, there are three ways to perform nested loop, hash join, merge join. The basic principle that should be followed is "we should not make any assumptions about the order of execution in an sql query."

[aafemt]

All of them require full read of the record stream for building hash-table, sorting or just make sure that there is no records left. And in any case "returning" that forms the record set is guaranteed by current implementation (which fills LTT) to finish all modifications before returning any result.