In this issue of General Bits you will find a collection of previously published articles All About Views.

Editor: elein at varlena.com

Expression columns in Views (#Issue 76-3) [GENERAL] virtual fields on VIEW? 18-Jun-2004

There is no limitation on the expressions you can put into the target list of a view. You can put on the target list any expression.

If your view is updateable, you will want to modify the INSERT/DELETE/UPDATE triggers on your view in order to handle the generated elements in the way that you see fit.

Here is one example of view definitions using expressions in their target lists.

        SELECT ...
          CASE
            WHEN date < CURRENT_DATE THEN 'green'::text
            WHEN date > CURRENT_DATE THEN 'red'::text
            ELSE 'blue'::text
          END
          AS state,
          (t1.field2 || t2.fieldA) AS stuff
        FROM ...
 

Keep in mind when you are producing reports and other displaying output that you may want to have data type formatting, colors, graphics, html formatting or other decorations in the output stream. Views are a good way to ensure that your display is consistent.

Contributors: Richard Huxton dev@archonet.com, raptorat tvskat.net@tvskat.net

Rules and views and cascading deletes (#Issue 68-2) [GENERAL] bug in delete rule ? 25-Mar-2004

Delete rules that delete from multiple tables where those tables are the basis of the view creates a catch-22 situation.

Rules, as you know, are also known as rewrite rules. When you create a rule on delete from myview ... that code is literally substituted with the rule logic.

For example, here is a view joins two tables on the id column:

        CREATE VIEW all_centers AS
           SELECT t0.center_id, t0.institution, t0.category,
              t0.street_number, t0.street_name, t0.postcode,
              t0.district_name, t0.city_name, t0.province_name,
              t0.country,
              t1.update_date, t1.number_of_beds, t1.chief,
              t1.phone_center, t1.fax_center
           FROM
              center t0, center_var t1
           WHERE
              t0.center_id = t1.center_id;
 

A rule is created on this view such that a delete on the view will delete the rows in both center and center_var. NOTE: This does not work as expected.

        CREATE RULE all_centers_del AS ON DELETE TO all_centers
           DO INSTEAD (
              DELETE FROM center_var WHERE center_id = OLD.center_id;
              DELETE FROM center WHERE center_id = OLD.center_id;
        );
 

Whenever a row is deleted from the view,

  DELETE FROM all_centers WHERE center_id = 5

        DELETE FROM center_var
        WHERE center_id =
           (select from all_centers where center_id = 5);

        DELETE FROM center
        WHERE center_id =
           (select center_id from all_centers where center_id = 5);
 

The solution for this dilemma depends on the relationships between the tables in the view. If there exists a foreign key relationship between the tables, then it is best to ensure that the foreign keys cascade on delete. Then when the master is deleted, the detail is also deleted. But if you use this, be very clear that the behavior reflects the relationship between the tables accurately.

This is what the foreign key relationship would look like if center is the master table and var_center is the detail table:

        CREATE TABLE center (
           id   INTEGER PRIMARY KEY,
                ...,
        )
        CREATE TABLE var_center (
           id   INTEGER,
                ...,
                PRIMARY KEY (id, var_id)
                FOREIGN KEY(id) REFERENCES center
              ON DELETE CASCADE
        )
 

With the DELETE CASCADE defined thus, then the delete rule on the view all_centers would change. The second delete, the one from center_vars, would be removed.

The original view with the cascade delete, does beg the question of the relationship between the tables. If there is a master detail relationship and cascade delete is defined, then all of the other details for that master will be deleted. Is this what is wanted by the original deletion rule? Or is it possible that there are two different tables with the same single unique primary key? If there are two tables with a single unique primary key, then why is the data in two separate tables? The answers to these questions depend on the intent of the schema design.

Contributors: Tom Lane tgl@sss.pgh.pa.us, elein elein@varlena.com, ferriol sylvain.ferriol@imag.fr, günter strubinsky strubinsky@acm.org

Updateable views (#Issue 82-4) How to create updateable views 15-Aug-2004

Although, there are several examples within different articles of General Bits covering updateable views, it seems from questions I've received that a consolidated example would be helpful.

In theory, all views are updateable if the rules are created. Simple single table views are the easiest and this includes views where all of the joins are only relational lookups.

Updateable views are implemented using RULES. A rule is evaluated once and only once when a command is issues. The command is re-written based on the directives of the rule system. This is different from a TRIGGER which is executed for each row selected by the original command.

In this example, we have people and a lookup table of addresses for the people. Also we have a lookup table of states for the address table.

        CREATE table people (
           person_id   SERIAL PRIMARY KEY,
           inits       text,
           fname       text
        );
        CREATE table states (
           state text PRIMARY KEY,
           state_long  text
        );
        CREATE table addresses (
           person_id   int REFERENCES people (person_id)
              ON DELETE CASCADE,
           city  text,
           state text REFERENCES states (state),
           country  text,
           zip   text
        );
 

A full view of these values would look like this:

        CREATE VIEW people_full AS
        SELECT p.person_id, p.inits, p.fname, a.city,
           a.state, s.state_long, a.country, a.zip
        FROM people p JOIN addresses a USING (person_id)
           JOIN states s USING (state);
 

        CREATE RULE "_RETURN" AS ON
        SELECT TO people_full DO INSTEAD
        SELECT p.person_id, p.inits, p.fname, a.city, a.state, s.state_long,
           a.country, a.zip FROM
           ((people p JOIN addresses a USING (person_id)) JOIN states s USING (state));
 

When creating UPDATE, INSERT and DELETE RULES, you have also access to NEW.column and/or OLD.column. The columns are the target list from the view. An INSERT only has NEW values; a DELETE only has OLD values and an UPDATE has them both.

To create an UPDATE RULE you must first decide what do do when various fields change. In the simple case, you can focus on the primary table in the view and ignore any other changes. This means that when any column in the people table is changed it should be written to the people table. Any changes in columns from the address or state fields should be ignored. It is safest to disallow the primary key does to changes on update. That RULE looks like this:

        CREATE RULE "upd_people_full" AS ON
        UPDATE TO people_full DO INSTEAD
        UPDATE people set inits=NEW.inits, fname=NEW.fname WHERE person_id=OLD.person_id;
 

An INSERT RULE would do the corresponding action for INSERT.

        CREATE RULE "ins_people_full" AS ON
        INSERT TO people_full DO INSTEAD
        INSERT INTO people (person_id, inits, fname) VALUES (NEW.person_id, NEW.inits, NEW.fname);
 

A DELETE RULE may be simpler or may be more complex. To keep things simple leverage the ON DELETE CASCADE and only delete from the people record. Then to DELETE the people record via the people_full view, the RULE would look like this.

        CREATE RULE "del_people_full" AS ON
        DELETE TO people_full DO INSTEAD
        DELETE FROM people WHERE person_id=OLD.person_id;
 

 DROP RULE "upd_people_full" on people_full;
 DROP RULE "ins_people_full" on people_full;
 CREATE RULE "upd_people_full" as ON
 UPDATE TO people_full DO INSTEAD
 (
        UPDATE people set inits=NEW.inits, fname=NEW.fname
           WHERE person_id=OLD.person_id;
        UPDATE addresses set city=NEW.city, state=NEW.state, zip=NEW.zip
           WHERE person_id=OLD.person_id;
 );
 

 CREATE RULE "ins_people_full" as ON
 INSERT TO people_full DO INSTEAD
 (
        INSERT INTO people (person_id, inits, fname)
           VALUES (nextval('people_person_id_seq'),NEW.inits, NEW.fname);
    INSERT INTO addresses (person_id,city, state, zip)
           VALUES (currval('people_person_id_seq'), NEW.city, NEW.state, NEW.zip );
 );
 

An exercise left for you is to see how the rules might change if you decided to allow a change in the state_long name in the state table.

A different DELETE RULE is not necessary since both tables were handled in the original RULE and via ON DELETE CASCADE.

PostgreSQL does not assume to know what you want with regards to the semantics of updateable views. The views PostgreSQL supports can be extremely complex with any number of subselects and JOINs.

The important idea with updateable views, is that you must know what exactly you want to happen when you update a view. Writing the rules is trivial, but thinking out the repercussions to the relations and semantics of your data is something only you can do clearly enough.

The SQL for this example is also available.

Contributors: elein elein@varlena.com

Deleting from Master Detail View (#Issue 99-4) [GENERAL] Is this correct behavior for ON DELETE rule? 25-Feb-2005

We have a master and a detail table of items and books. The manipulation of books is to be done strictly through a view, bookview. The insertion into a bookview causes an insertion into both the item table and the book table. A delete from the book view should delete both the item and the book records.

Usually all inserts, deletes and updates on views are done with DO INSTEAD rules. However, due to the scope of the OLD record in the RULE, the delete functionality is a bit tricky.

Here is the table, view and rule creation.drop table book cascade;

 DROP TABLE item CASCADE;
 DROP TABLE book CASCADE;

 -- master table
 CREATE TABLE item
       (id SERIAL PRIMARY KEY,
       type  text,
       title text
 );

 -- detail table
 CREATE TABLE book
       (id     integer REFERENCES item PRIMARY KEY,
       title text,
       author  text
 );

 -- combine stuff from item and book tables
 CREATE VIEW bookview AS
       SELECT i.id, b.title, b.author FROM item i, book b
       WHERE i.id=b.id;

 -- insert to item and book instead of bookview
 CREATE RULE bookviewins AS ON INSERT TO bookview DO INSTEAD (
       INSERT INTO item (type, title)
       VALUES ('book', NEW.title);
       INSERT INTO book (id, title, author)
       VALUES (currval('item_id_seq'), NEW.title, NEW.author);
 );

 -- delete to item and book instead of bookview
 CREATE RULE bookviewdel AS ON DELETE TO bookview DO INSTEAD (
       DELETE FROM book WHERE id=OLD.id;
       DELETE FROM item WHERE id=OLD.id;
 );
 INSERT INTO bookview (title, author) VALUES ('Dune','Herbert');
 INSERT INTO bookview (title, author) VALUES ('Hobbit','Tolkein');

 db=# SELECT * FROM bookview;
  id | title  | author
 ----+--------+---------
   1 | Dune   | Herbert
   2 | Hobbit | Tolkein
 (2 rows)

 db=# DELETE FROM bookview WHERE author='Tolkein';
 DELETE 0

 db=#  SELECT * FROM bookview;
  id | title | author
 ----+-------+---------
   1 | Dune  | Herbert
 (1 row)

 db=#  SELECT * FROM item;
  id | type | title
 ----+------+--------
   1 | book | Dune
   2 | book | Hobbit
 (2 rows)

 db=#  SELECT * FROM book;
  id | title | author
 ----+-------+---------
   1 | Dune  | Herbert
 (1 row)
 

The proper way to enforce the deletion of both the book and item is to define the relationships between book and item so that delete cascades.

 CREATE TABLE book
       (id     integer REFERENCES item ON DELETE CASCADE PRIMARY KEY,
       title text,
       author  text
 );
 

 CREATE RULE bookviewdel AS ON DELETE TO bookview DO INSTEAD (
       DELETE FROM item WHERE id=OLD.id;
 );
 

 db=# SELECT * FROM bookview;
  id | title | author
 ----+-------+---------
   1 | Dune  | Herbert
 (1 row)

 db=# DELETE FROM bookview WHERE author='Tolkein';
 DELETE 0
 db=# SELECT * FROM bookview;
  id | title | author
 ----+-------+---------
   1 | Dune  | Herbert
 (1 row)
 db=# SELECT * FROM item;
  id | type | title
 ----+------+-------
   1 | book | Dune
 (1 row)

 db=# -- shows both books
 db=# SELECT * FROM book;
  id | title | author
 ----+-------+---------
   1 | Dune  | Herbert
 (1 row)


 

Contributors: Tom Lane tgl@sss.pgh.pa.us, Bruce Momjian pgman@candle.pha.pa.us, Rick Schumeyer rschumeyer@ieee.org, Keary Suska hierophant@pcisys.net

Materialized Views in PostgreSQL (#Issue 64-1) [SQL] Materialized View Summary 24-Feb-2004

A materialized view is a technique to "cache" the results of a query when its result set is large or the query is very slow and that query is used very often. A materialized view is a set of query results stored in a table. Usually this means the results of a selection of a view. Queries involving aggregation are a good reason to have a materialized view. Usually a slow query is not a good reason to have a materialized view unless you have exhausted the best advice from pgsql-general and pgsql-performance.

When an ordinary view is selected, the view is expanded into its underlying query and that query is executed in full for each statement.

A materialized view is a managed table. It is populated when the underlying tables of its base query are updated so that it always exists.

There are several techniques for creating materialized views which are described in Jonathan Gardner's paper Materialized Views in PostgreSQL.

It is interesting to note that the materialized views' issues are very similar to the issues for replication. The copy of the data is done either from a database table to another database table for replication or an executed view into a copy of the data into the same database for materialized views. Decisions with regards to how to and when to update the data copy are common to both sets of problems. In fact any denormalization or data copies will have the same basic question: How up to date is the copy of the data and how "expensive" is it to keep it updated?

Contributors: elein elein@varlena.com, Jonathan Gardner jgardner@jonathangardner.net