Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs

On 18th of July, Stephen Frost committed patch:

WITH CHECK OPTION support for auto-updatable VIEWs
 
For simple views which are automatically updatable, this patch allows
the user to specify what level of checking should be done on records
being inserted or updated.  For 'LOCAL CHECK', new tuples are validated
against the conditionals of the view they are being inserted into, while
for 'CASCADED CHECK' the new tuples are validated against the
conditionals for all views involved (from the top down).
 
This option is part of the SQL specification.
 
Dean Rasheed, reviewed by Pavel Stehule

Continue reading Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs

Waiting for 9.3 – Add a materialized view relations.

On 4th of March, Kevin Grittner committed patch:

Add a materialized view relations.
 
A materialized view has a rule just like a view and a heap and
other physical properties like a table.  The rule is only used to
populate the table, references in queries refer to the
materialized data.
 
This is a minimal implementation, but should still be useful in
many cases.  Currently data is only populated "on demand" by the
CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW statements.
It is expected that future releases will add incremental updates
with various timings, and that a more refined concept of defining
what is "fresh" data will be developed.  At some point it may even
be possible to have queries use a materialized in place of
references to underlying tables, but that requires the other
above-mentioned features to be working first.
 
Much of the documentation work by Robert Haas.
Review by Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja
Security review by KaiGai Kohei, with a decision on how best to
implement sepgsql still pending.

Continue reading Waiting for 9.3 – Add a materialized view relations.

Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax

On 1st of February, Peter Eisentraut committed patch:

Add CREATE RECURSIVE VIEW syntax
 
This is specified in the SQL standard.  The CREATE RECURSIVE VIEW
specification is transformed into a normal CREATE VIEW statement with a
WITH RECURSIVE clause.
 
reviewed by Abhijit Menon-Sen and Stephen Frost

Continue reading Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax

Waiting for 9.3 – Support automatically-updatable views.

On 8th of December, Tom Lane committed patch:

Support automatically-updatable views.
 
This patch makes "simple" views automatically updatable, without the need
to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
are those classified as updatable according to SQL-92 rules.  The rewriter
transforms INSERT/UPDATE/DELETE commands on such views directly into an
equivalent command on the underlying table, which will generally have
noticeably better performance than is possible with either triggers or
user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
continues to operate the same as before.
 
For the moment, security_barrier views are not considered simple.
Also, we do not support WITH CHECK OPTION.  These features may be
added in future.
 
Dean Rasheed, reviewed by Amit Kapila

Continue reading Waiting for 9.3 – Support automatically-updatable views.

Waiting for 9.1 – triggers on views

On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:

Support triggers ON views.
 
This patch adds the SQL-standard concept OF an INSTEAD OF TRIGGER, which
IS fired instead OF performing a physical INSERT/UPDATE/DELETE.  The
TRIGGER FUNCTION IS passed the entire OLD AND/OR NEW ROWS OF the VIEW,
AND must figure OUT what TO do TO the underlying TABLES TO implement
the UPDATE.  So this feature can be used TO implement updatable views
USING TRIGGER programming STYLE rather than rule hacking.
 
IN passing, this patch corrects the names OF SOME COLUMNS IN the
information_schema.triggers VIEW.  It seems the SQL committee renamed
them somewhere BETWEEN SQL:99 AND SQL:2003.
 
Dean Rasheed, reviewed BY Bernd Helmle; SOME additional hacking BY me.

Continue reading Waiting for 9.1 – triggers on views