Updatable Views and INSERT INTO ... ON CONFLICT
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
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