Helpers for MERGE INTO

[mielvds]

Since version 15, PG supports MERGE INTO. I managed to use this library for such query, but I was missing some of the helpers that are available for insert on conflict, for example.

This is more or less what I came up with (primaryKeys and columns are instances of ColumnSet):

const mergeQuery = pgp.as.format(`
            MERGE INTO $<to:name> x
            USING $<from:name> y
            ON ${primaryKeys.columns.map(c => `x.${c.escapedName} = y.${c.escapedName}`).join(' AND ')}
            WHEN MATCHED THEN
                UPDATE SET ${columns.assignColumns({from: 'y'})} 
            WHEN NOT MATCHED THEN
                INSERT (${columns.names}) VALUES (${columns.columns.map(c => `y.${c.escapedName}`).join(',')});
            `, { to, from }) 

from which I distill the following suggestions for ColumnSet:

• .joinColumns({from: 'y', to: 'x'}) or .assignColumns({from: 'y', sep: 'AND'}) to replace .columns.map(c => `x.${c.escapedName} = y.${c.escapedName}`).join(' AND ')
• .names({from: 'y'}) or .assignValues({from: 'y'}) to replace .columns.map(c => `y.${c.escapedName}`).join(',')

These are just examples, of course, and any more profound support for MERGE INTO similar to pgp.helpers.insert() would be even nicer. WDYT?

[vitaly-t]

@mielvds

Looks like you can easily write a helper function for yourself that produces such a query?

Other than that, such a query is too verbose to consider a generic implementation. It is way more complex than the queries produced by this library.

The extras it has, on top of what other query generators use:

• Source + Destination names, not just one name
• List of primary keys can vary
• UPDATE logic can vary

And that's too much to consider adding it.

One piece that perhaps could be considered - adding separator parameter for assignColumns method. But then, it is no longer an assignment operation, as its logic becomes an equality check. So, there's that.

In all, I have to assign it a won't do label, I'm afraid, and move it into Discussions.