One of the annoying things about PostgreSQL unlike some other databases we have worked with is that simple views are not automatically updateable. There is some work involved to make views updateable. For simple views, this is annoying, but for more complex views it is a benefit to be able to control how things are updated. In a later version of PostgreSQL perhaps 8.4 or 8.5 this will be ratified and PostgreSQL will enjoy the same simplicity of creating simple updateable views currently offered by MySQL and SQL Server and other DBMSs, but still allow for defining how things should be updated for more complex views. For this exercise we are using PostgreSQL 8.2.5, but most of it should work for lower versions with slight modification.
For this exercise, we shall create a fairly complex updateable view to demonstrate how one goes about doing this.
Here is a scenario where being able to control how a view is updated comes in very handy.
We all know relational databases are great because they give you great mobility on how you slice and dice information. At times for data entry purposes, the good old simple flat file is just more user-friendly.
Problem: You are developing an inventory application for a molecular biology lab and they have the following requirements:
They have 2 projects going on. One on Multiple Sclerosis Research (MS) and one on Alzheimer's. Each is funded by different grants and for grant cost allocation purposes, they need to keep track of the supplies they use on each project.
How do you present a flat file inventory entry screen, but behind the scenes have a inventory and inventory transaction scheme so you can run period reports and aggregate summaries and have automatic totaling?
Possible Solution: One way to do it is with a crosstab summary view that is updateable. Views are incredibly useful abstraction tools.
You do that in PostgreSQL by creating insert, update, and delete rules on your views. For our particular case, we will not be allowing deletion so we will not have a delete rule.
In our system we have 2 tables for simplicity. inventory and inventory_flow. I know we should have a project lookup table or in 8.3 possibly use an ENUM, but to make this short, we are skipping that.
CREATE TABLE inventory
(
item_id serial NOT NULL,
item_name varchar(100) NOT NULL,
CONSTRAINT pk_inventory PRIMARY KEY (item_id),
CONSTRAINT inventory_item_name_idx UNIQUE (item_name)
)
WITH (OIDS=FALSE);
CREATE TABLE inventory_flow
(
inventory_flow_id serial NOT NULL,
item_id integer NOT NULL,
project varchar(100),
num_used integer,
num_ordered integer,
action_date timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_inventory_flow PRIMARY KEY (inventory_flow_id),
CONSTRAINT fk_item_id FOREIGN KEY (item_id)
REFERENCES inventory (item_id)
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
CREATE VIEW vwinventorysummary As
SELECT i.item_id, i.item_name,
SUM(CASE WHEN iu.project = 'Alzheimer''s'
THEN iu.num_used ELSE 0 END) As total_num_used_altz,
SUM(CASE WHEN iu.project = 'MS' THEN iu.num_used ELSE 0 END) As total_num_used_ms,
CAST(NULL As integer) As add_num_used_altz,
CAST(NULL As integer) As add_num_used_ms,
CAST(NULL As integer) As add_num_ordered,
SUM(COALESCE(iu.num_ordered,0)) - SUM(COALESCE(iu.num_used,0)) As num_remaining
FROM inventory i LEFT JOIN inventory_flow iu ON i.item_id = iu.item_id
GROUP BY i.item_id, i.item_name;
CREATE RULE updinventory AS
ON UPDATE TO vwinventorysummary
DO INSTEAD (
UPDATE inventory
SET item_name = NEW.item_name WHERE inventory.item_id = NEW.item_id;
INSERT INTO inventory_flow(item_id, project, num_used, num_ordered)
SELECT NEW.item_id, 'Alzheimer''s', NEW.add_num_used_altz, 0
WHERE NEW.add_num_used_altz IS NOT NULL;
INSERT INTO inventory_flow(item_id, project, num_used, num_ordered)
SELECT NEW.item_id, 'MS', NEW.add_num_used_ms, 0
WHERE NEW.add_num_used_ms IS NOT NULL;
INSERT INTO inventory_flow(item_id, project, num_used, num_ordered)
SELECT NEW.item_id, 'Resupply', 0, NEW.add_num_ordered
WHERE NEW.add_num_ordered IS NOT NULL;);
CREATE RULE insinventory AS
ON INSERT TO vwinventorysummary
DO INSTEAD (
INSERT INTO inventory (item_name) VALUES (NEW.item_name);
INSERT INTO inventory_flow (item_id, project, num_used)
SELECT i.item_id AS new_itemid, 'Altzeimer''s', NEW.add_num_used_altz
FROM inventory i
WHERE i.item_name = NEW.item_name
AND NEW.add_num_used_altz IS NOT NULL;
INSERT INTO inventory_flow (item_id, project, num_used)
SELECT i.item_id AS new_item_id, 'MS', NEW.add_num_used_ms
FROM inventory i
WHERE i.item_name = NEW.item_name AND
NEW.add_num_used_ms IS NOT NULL;
INSERT INTO inventory_flow (item_id, project, num_ordered)
SELECT i.item_id AS new_item_id, 'Initial Supply', NEW.add_num_ordered
FROM inventory i
WHERE i.item_name = NEW.item_name AND
NEW.add_num_ordered IS NOT NULL;
);
Now look at what happens when we insert and update our view
--NOTE: here we are using the new multi-row valued insert feature introduced in 8.2
INSERT INTO vwinventorysummary(item_name, add_num_ordered)
VALUES ('Phenol (ml)', 1000), ('Cesium Chloride (g)', 20000),
('Chloroform (ml)', 10000), ('DNA Ligase (ml)', 100);
UPDATE vwinventorysummary
SET add_num_used_ms = 5, add_num_used_altz = 6 WHERE item_name = 'Cesium Chloride (g)';
UPDATE vwinventorysummary SET add_num_used_ms = 2 WHERE item_name = 'Phenol (ml)';
UPDATE vwinventorysummary SET item_name = 'CSCL (g)' WHERE item_name = 'Cesium Chloride (g)';
The slick thing about this is if you were to create a linked table in something like say Microsoft Access and designated item_id as the primary key, then the user could simply open up the table and update as normally and behind the scenes the rules would be working to do the right thing.