Database Reference
In-Depth Information
We create a very simple view on top of it like the following:
CREATE VIEW cust_view AS
SELECT customerid
,firstname
,lastname
,age
FROM cust;
Each row in our view corresponds to one row in a single-source table and each column is
referred to directly without a function call. So we expect to be able to make inserts, updates,
and deletes pass through our view into the base table.
The following examples are all views where inserts, updates, and deletes cannot easily be
made to flow to the base table.
If we had views that look like the following:
CREATE VIEW cust_avg AS
SELECT avg(age)
FROM cust;
CREATE VIEW cust_above_avg_age AS
SELECT customerid
,substr(firstname, 1, 20) as fname
,substr(lastname, 1, 20) as lname
,age -
(SELECT avg(age)::integer
FROM cust) as years_above_avg
FROM cust
WHERE age >
(SELECT avg(age)
FROM cust);
CREATE VIEW potential_spammers AS
SELECT customerid
FROM cust
ORDER BY spam_score(firstname, lastname) DESC
LIMIT 100;
So, before we proceed to the steps to allow any/all of inserts, updates, or deletes to flow from
views to base tables, we need to be clear about whether that makes sense conceptually.
How to do it...
PostgreSQL provides a facility to create query rewrite rules. These are in some ways similar
to Oracle's instead-of triggers; though no other database has an exactly similar concept to
PostgreSQL rules.
