[Feature]: Support for MERGE syntax

[kewinbrand]

What happened?

We are evaluating Babelfish for a long time now but are facing some basic problems due to the missing syntax "MERGE", it is heavily usesd by some CDC tools like DEBEZIUM. We also plan to use it with web development, where libraries like EF Core also rely on this syntax. Is there any plan to support in the near future?

Version

BABEL_4_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[robverschoor]

T-SQL MERGE supports a number of commonly used syntactic and semantic aspects which PG does currently not support. Right now, the best workaround is to use the -rewrite feature of BabelfishCompass, but this is useful only when you control the actual SQL code (so with frameworks it may not be a solution). For PG 17, it looks like some (not all) of these aspects will be addressed, so we should re-evaluate whether Babelfish can use that. In the mean time, could you please confirm whether your MERGE statements contain any of the following, which PG 17 does not seem to be supporting (as it looks currently):

• the target table is an updatable view
• the target table is a CTE
• use of recursive CTE in the USING clause is supported

[kewinbrand]

we do not use any of these statements you mentioned for now we are considering pointing the CDC tool directly to the underlying babelfish postgres. we have tested the basics and it seems to have had no problems. is there any documentation or guide that explains the risks of doing this?