From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Supporting MERGE on updatable views |
Date: | 2022-12-08 10:03:29 |
Message-ID: | CAEZATCVcB1g0nmxuEc-A+gGB0HnfcGQNGYH7gS=7rq0u0zOBXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I have been playing around with making updatable views support MERGE,
and it looks to be fairly straightforward.
I'm intending to support auto-updatable views, WITH CHECK OPTION, and
trigger-updatable views, but not views with rules, as I think that
would be more trouble than it's worth.
Per the SQL standard, if the view isn't auto-updatable, it requires
the appropriate INSTEAD OF INSERT/UPDATE/DELETE triggers to perform
the merge actions. One limitation with the current patch is that it
will only work if the view is either auto-updatable with no INSTEAD OF
triggers, or it has a full set of INSTEAD OF triggers for all
INSERT/UPDATE/DELETE actions mentioned in the MERGE command. It
doesn't support a mix of those 2 cases (i.e., a partial set of INSTEAD
OF triggers, such as an INSTEAD OF INSERT trigger only, on an
otherwise auto-updatable view). Perhaps it will be possible to
overcome that limitation in the future, but I think that it will be
hard.
In practice though, I think that this shouldn't be very limiting -- I
think it's uncommon for people to define INSTEAD OF triggers on
auto-updatable views, and if they do, they just need to be sure to
provide a full set.
Attached is a WIP patch, which I'll add to the next CF. I still need
to do more testing, and update the docs, but so far, everything
appears to work.
Regards,
Dean
Attachment | Content-Type | Size |
---|---|---|
support-merge-into-view-v1.patch | text/x-patch | 106.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2022-12-08 10:40:58 | Re: Aggregate node doesn't include cost for sorting |
Previous Message | Peter Eisentraut | 2022-12-08 09:27:25 | Re: refactor ExecGrant_*() functions |