I wrote:
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.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