PostgreSQL 9.3 Will Feature UPDATEable Views 68
Picking up work abandoned around Postgres 8.2, a patch recently hit the PostgreSQL 9.3 branch that adds SQL-92 automatically updatable views. For many common cases, you will no longer have to write hairy triggers to fake UPDATE support (e.g. if you have a view that hides a few internal columns). Limitations currently include only supporting views with at most one table in the FROM clause. This complements the under-advertised INSTEAD OF trigger support added in 9.1.
Good use-case? (Score:5, Interesting)
Back when I was working w/ Oracle (10 i think) I remember trying out update-able views, but I found that the rules and restrictions were such that it seemed like only trivial views could be updated and I decided I was easier to just work w/ the underlying tables and/or write sprocs whenever I need to do more far-reaching updates.
That said, I never dug that deep into the subject. Do any slashdotters know of a situation where an update-able view would be handy/ideal?
The Rules System (Score:3, Interesting)
This is a nice feature for standardization (and thus, compatibility and portability with other SQL systems) but it's also important to know that PostgreSQL also has a "rules" system that allows for much more complicated view/table relationships. Rules allow for you to redirect the new and old values to updates, inserts and delete statements across as many tables/rows as required based on query being run against a view. It is very similar to a trigger really.
Read more about it here: http://www.postgresql.org/docs/9.2/static/rules.html
I used the Rules system to handle most of the security within The Schemaverse (schemaverse.com), an application written completely within a PostgreSQL database.