PostgreSQL 9.3 Will Feature UPDATEable Views

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.

Re:Good use-case?

[magarity]

Do any slashdotters know of a situation where an update-able view would be handy/ideal?

Teradata is usually set up so that every basic DML operation (insert, update, delete) is done via views.

An improvement

[jellomizer]

It is a nice feature... However I don't see it too useful for the way I work.
Being that if I have a view it is often because it needs to have many tables involved. And I would need to setup an on Insert or on Update rule on the view anyways.

Re:Good use-case?

[Anonymous Coward]

it seemed like only trivial views could be updated

Sadly true, the UPDATE has to be merged with the SELECT the view is built from to give an executable SQL. That's not always trivial to do.

Do any slashdotters know of a situation where an update-able view would be handy/ideal?

AFAIK the most common uses are:
a) Providing restricted views of tables to users with lower privileges
(eg a view of a users table where a connected user can read username, email etc but not password hash)
b) Providing a view for backwards compatibility with older applications when a table schema is updated, to avoid breaking queries in the older application until it is updated.

That's a weirdly specific topic to post on /.

[mobby_6kl]

But nevertheless quite interesting. The idea of updatable views is certainly a good one, but it seems that the current limitations make this feature more or less useless for now:

• The view must have exactly one entry in its FROM, which must be a table or another updatable view.
• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
• All columns in the viewâ(TM)s select list must be simple references to columns of the underlying relation. They cannot be expressions, literals or functions. System columns cannot be referenced, either.
• No column of the underlying relation can appear more than once in the viewâ(TM)s select list.
• The view must not have the security_barrier property.

Re:Good use-case?

[Anrego]

Only two use cases I can see are restricting access to a subset of columns (in which case a server side procedure works fine), or to allow legacy apps to work within new schemas (although this sounds like a really messy solution, and has that whole "temporary fix that gets left in forever" feel to it).

Re:Good use-case?

[ArsenneLupin]

Do any slashdotters know of a situation where an update-able view would be handy/ideal?

Two pre-existing applications accessing the same data, and each of them expects the data to be laid out in a (slightly different) schema.

So, define tables with a common physical representation of the data, and then define views to map that representation to each one of the application's expectations.

O, and only automatically updatable views have restrictions that make them useless. The INSTEAD OF trigger allows to make almost any view updatable, but you need to tell the DB how.

Re:What about materialized views?

[schmiddy]

Materialized views are on the way, hopefully for 9.3 [postgresql.org]. The first pass at this is fairly limited, you have to refresh the matview yourself (i.e. it supports only the "snapshot" type of matview maintenance, per terminology here [jonathangardner.net]).