Chapter 5 part 5 - Views

Views

A view is the representation of a query, stored in the system catalogue for quick access. All the objects involved in the view are translated to the internal identifiers at the creation time; the same happens for any wild card which is expanded to the column list. An example will explain better the concept. Let's create a simple table. Using the generate_series() function let's put some data into it.

CREATE TABLE t_data 
        ( 
                i_id serial,
                t_content       text
        );

ALTER TABLE t_data 
ADD CONSTRAINT pk_t_data PRIMARY KEY (i_id);


INSERT INTO t_data
        (
                t_content
        )
SELECT
        md5(i_counter::text)
FROM
        (
                SELECT
                        i_counter
                FROM
                        generate_series(1,200) as i_counter
        ) t_series;

CREATE OR REPLACE VIEW v_data 
AS 
  SELECT 
          *
  FROM 
        t_data;

The SELECT * from t_data or v_data looks exactly the same, the view simply runs the stored SQL used at creation time. If we look to the stored definition in pg_views we'll find the wildcard is expanded into the table's columns.

 db_test=# SELECT * FROM pg_views where viewname='v_data';
-[ RECORD 1 ]--------------------
schemaname | public
viewname   | v_data
viewowner  | postgres
definition |  SELECT t_data.i_id,
           |     t_data.t_content
           |    FROM t_data;

Now let's add a new column to the t_data table and run again the select on the table and the view.

 ALTER TABLE t_data ADD COLUMN d_date date NOT NULL default now()::date;
 
 db_test=# SELECT * FROM t_data LIMIT 1;
 i_id |            t_content             |   d_date   
------+----------------------------------+------------
    1 | c4ca4238a0b923820dcc509a6f75849b | 2014-05-21
(1 row)


db_test=# SELECT * FROM v_data LIMIT 1;
 i_id |            t_content             
------+----------------------------------
    1 | c4ca4238a0b923820dcc509a6f75849b
(1 row)

The view doesn't show the new column. To update the view definition a new CREATE OR REPLACE VIEW statement must be issued.

 CREATE OR REPLACE VIEW v_data 
AS 
  SELECT 
        *
  FROM 
        t_data;
        
db_test=# SELECT * FROM v_data LIMIT 1;
 i_id |            t_content             |   d_date   
------+----------------------------------+------------
    1 | c4ca4238a0b923820dcc509a6f75849b | 2014-05-21
(1 row)

Because the views are referring the objects identifiers they will never invalidate when the referred objects are altered. The CREATE OR REPLACE statement updates the view definition only if the column list adds new attributes in the end. Otherwise, any change to the existing columns requires the view's drop and recreate.
When one or more view are pointing a relation this cannot be dropped. The option CASCADE in the drop statement will drop the dependant objects before the final drop. This is a dangerous approach though. Dropping objects regardless can result in data or functionality loss.
When a drop is blocked by dependant objects the database emits a message with the informations about the dependencies. If the amount of objects is too much big it's better to query the pg_depend table to find out the correct dependencies. This table lists all the dependencies for each object using a peculiar logic. We'll take a deep look in 5.8.
As seen before a view is a logical short cut to a pre saved query. This means the database will follow all the steps to execute exactly the same way if the entire query has been sent via client, except for the network overhead.
Nothing forbids a view to point another view inside the definition or join the one or more views in a different query. This can cause massive regression on the overall performance because each view require an execution plan and mixing the views will cause not efficient planning.
To mark a relation is a view it's a good idea to use a naming prefix like v_. This will distinguish them from the tables marked with the prefix t_. In 9 we'll take a to the naming conventions to let the database schema self explanatory.
PostgreSQL from the version 9.3 supports the updatable simple views. A view is simple if

• Have exactly one entry in its FROM list, which must be a table or another updatable view
• Does not contain WITH, DISTINCT, GROUP BY, HAVING,LIMIT, or OFFSET clauses at the top level
• Does not contain set operations (UNION, INTERSECT or EXCEPT) at the top level
• All columns in the view'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
• columns of the underlying relation do not appear more than once in the view's select list
• does not have the security_barrier property

If the view doesn't fit those rules it's still possible to make it updatable using the triggers with the INSTEAD OF clause.
The major version 9.3 introduces also the materialised view concept. This is a physical snapshot of the saved SQL and can be refreshed with the statement REFRESH MATERIALIZED VIEW.