Follow Slashdot stories on Twitter


Forgot your password?

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.
This discussion has been archived. No new comments can be posted.

PostgreSQL 9.3 Will Feature UPDATEable Views

Comments Filter:
  • Good use-case? (Score:5, Interesting)

    by nobodyman ( 90587 ) * on Wednesday December 12, 2012 @12:16PM (#42262315) Homepage

    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)

    by Anonymous Coward on Wednesday December 12, 2012 @01:20PM (#42263157)

    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:

    I used the Rules system to handle most of the security within The Schemaverse (, an application written completely within a PostgreSQL database.

You scratch my tape, and I'll scratch yours.