Whoops!

UPDATE, INSERT, and DELETE Not equivalent to a table after all There are workarounds. Making the DBA earn her keep. The easier something is to use... The more work went in to making it that way.

Enter complexity

Lots of work for the DBA. Lots of chances for things to go wrong. Documentation becomes essential (for all)

Performance:

Not too bad, but never as good as doing it yourself. Well, not never, but it is possible to do better.

Transparency:

Sometimes, you want to see what is going on Good tools: \d and EXPLAIN Again, documentation

The SELECT * trap

"Compile-time" views:

CREATE TABLE person {
  id  SERIAL UNIQUE,
  fullname TEXT
);
INSERT INTO person(fullname) VALUES ('Sam Spade');

CREATE TABLE fruitcart (
  person INTEGER NOT NULL,
  apples INTEGER NOT NULL DEFAULT 0,
  pears INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT "fruitcart_person" FOREIGN KEY (person)
    REFERENCES person(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO fruitcart VALUES (1,10,3);

CREATE VIEW fruit AS
  SELECT p.fullname, f.* FROM fruitcart f, person p
  WHERE f.person = p.id;

oscon=> SELECT * FROM fruitcart;
 person | apples | pears
--------+--------+------
      1 |     10 |     3


oscon=> SELECT * FROM fruit;
 fullname  | person | apples | pears
-----------+--------+--------+------
 Sam Spade |      1 |     10 |     3


ALTER TABLE fruitcart ADD bananas INGTEGER;
UPDATE fruitcart SET bananas=5 WHERE person=1;
ALTER TABLE fruitcart ALTER bananas SET DEFAULT 0;
ALTER TABLE fruitcart ALTER bananas SET NOT NULL;

oscon=> SELECT * FROM fruitcart;
 person | apples | pears | bananas
--------+--------+-------+--------
      1 |     10 |     3 |       5


oscon=> SELECT * FROM fruit;
 fullname  | person | apples | pears
-----------+--------+--------+------
 Sam Spade |      1 |     10 |     3

Yes, we have no bananas