PG Bug reporting form <noreply@postgresql.org> writes:
> create table foo (id int);
> with cte_failure as (
> merge into foo as target
> using foo as source
> on target.id = source.id
> when matched then do nothing
> )
> select 'fail!';
> When executing the above code I get the following error:
> ERROR: DO INSTEAD NOTIFY rules are not supported for data-modifying
> statements in WITH
With asserts on, it fails in the parser:
TRAP: FailedAssertion("IsA(cte->ctequery, InsertStmt) || IsA(cte->ctequery, UpdateStmt) || IsA(cte->ctequery,
DeleteStmt)",File: "parse_cte.c", Line: 149, PID: 303950)
postgres: postgres regression [local] SELECT(ExceptionalCondition+0x7c)[0x98013c]
postgres: postgres regression [local] SELECT(transformWithClause+0x66c)[0x6275ec]
postgres: postgres regression [local] SELECT(transformStmt+0x10f9)[0x603619]
> I suspect that MERGE was never intended to work as a CTE, but right now the
> grammar allows any PreparableStmt in a common_table_expr and the above error
> is triggered a lot later due to implementation details.
It evidently wasn't ever *tested*, but in principle I think it ought
to work. I'm not sure how much effort will be involved to make that
happen. At this point we might have to disallow it for v15 and
come back to the problem later.
regards, tom lane