Re: UPSERT on a view

I wrote:
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve(at)rothskeller(dot)net> wrote:
>>> More generally: how can one write trigger functions for a view (that is
>>> not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE
>>> will work with the correct semantics?

> Don't think it's possible unfortunately. The trigger would have to take
> care of the situation, but it doesn't have any way to know that the
> calling query has an ON CONFLICT rule.

BTW, I don't suppose it solves your problem, but ON CONFLICT does work
with auto-updatable views:

regression=# CREATE TABLE ttest (x integer primary key);
CREATE TABLE
regression=# CREATE VIEW vtest AS SELECT x FROM ttest;
CREATE VIEW
regression=# INSERT INTO vtest VALUES (1);
INSERT 0 1
regression=# INSERT INTO vtest VALUES (1);
ERROR: duplicate key value violates unique constraint "ttest_pkey"
DETAIL: Key (x)=(1) already exists.
regression=# INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
INSERT 0 0

The query rewriter knows how to translate the ON CONFLICT to apply
to the underlying table along with the rest of the command.

regards, tom lane