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

From: "Joel Jacobson" <[email-masked]>
Date: Sat, 01 Jan 2022 13:52:43 +0100

Hi,

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

One imaginable pattern is when a user wants to refactor by renaming a table,
but for some reason cannot refactor some specific application and want to
allow it to continue to use the table's old name.

One approach to do so would be to create a an Updatable View (aka Simple view) [1],
given the same name as the table's old name.

This is ugly and not something I would do myself, but I've read about how others describe this pattern, not in the context of ON CONFLICT, but in general, when refactoring.

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

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

/Joel

---

From: [email-masked]
Date: Fri, 2 Sep 2022 12:04:59 +0200

> I note it's not yet possible to INSERT INTO an Updatable View using the 

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 

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