Is it possible to map MERGE onto a query with CTEs that does the the
various DMLs, with all but the last RETURNING? Here's a sketch:
WITH matched_rows AS (
SELECT FROM <target> t WHERE <condition>
),
updated_rows AS (
UPDATE <target> t
SET ...
WHERE ... AND t in (SELECT j FROM matched_rows j)
RETURNING t
),
inserted_rows AS (
INSERT INTO <target> t
SELECT ...
WHERE ... AND t NOT IN (SELECT j FROM matched_rows j)
RETURNING t
),
DELETE FROM <target> t
WHERE ...;
Now, one issue is that in PG CTEs are basically like temp tables, and
also like optimizer barriers, so this construction is not online, and if
matched_rows is very large, that would be a problem.
As an aside, I'd like to be able to control which CTEs are view-like and
which are table-like. In SQLite3, for example, they are all view-like,
and the optimizer will act accordingly, whereas in PG they are all
table-like, and thus optimizer barriers.
Nico
--
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
