Re: Updatable Views and INSERT INTO ... ON CONFLICT

Joel Jacobson:
> I note it's not yet possible to INSERT INTO an Updatable View using the
> ON CONFLICT feature.

To be clear, it seems to be supported for AUTO-updatable views and for
views with manually created RULES, but not for views with INSTEAD OF
triggers.

> Not saying it is desired, just trying to better understand the limits of
> Updatable Views.

It's certainly desired. I tried to use it in the past.

> Are there reasons why it would not be possible to develop support INSERT
> INTO ... ON CONFLICT for Updatable Views?

I think the main challenge is, that when a view has an INSTEAD OF insert
trigger, the INSERT statement that is in the trigger function is not the
same statement that is called on the view. Auto-updatable views rewrite
the original query, so they can support this.

For this to work, the outer INSERT would have to "catch" the error that
the trigger function throws on a conflict - and then the outer INSERT
would have to execute an UPDATE on the view instead.

I don't know about the internals of INSERT .. ON CONFLICT, but I'd
assume the conflict handling + update happens much later than calling
the instead of trigger, so that makes it impossible to do it right now.

Best

Wolfgang