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