CREATE VIEW observations_for_plate AS
SELECT observation.observation_id AS "Observation ID",
observation.plate_id AS "Plate ID",
observation.date AS "Date",
observation.alive AS "Alive",
observation.dead AS "Dead",
observation.censor AS "Censor"
FROM observation
ORDER BY observation.plate_id, observation.date;
For example, I'd like to change the data in the Dead column in the third row to "2". I know this can be done with the SQL:
UPDATE observations_for_plate SET "Dead"=2 WHERE "Observation ID"=20000040;
But I'd like to be able to do the same thing using the form.
Further, it would be desirable to do the same thing but within a more complex view (Screen Shot 2) like the following:
CREATE VIEW observations_for_plate AS
SELECT observation.observation_id AS "Observation ID",
observation.plate_id AS "Plate ID",
observation.date AS "Date",
observation.alive AS "Alive",
observation.dead AS "Dead",
observation.censor AS "Censor",
tech.tech_name AS "Tech Name"
FROM observation
LEFT JOIN tech ON observation.tech_id=tech.tech_id
ORDER BY observation.plate_id, observation.date;
However, in Postgres it seems that the same UPDATE statement doesn't work when the view selects from more than one table, and that a trigger or rule needs to be set up to do an update. But it would be great to be able to use the form to edit/update the data in this circumstance.
Thanks!