I wrote a trigger function to convert inserts into updates if the
record exists already.
- I am not using rules because the table is populated via COPY.
- I am employing a two-stage process (PERFORM, then UPDATE) because
the update trigger might decide not to update after all, and therefore
FOUND cannot be used to indicate the record's existence.
Is this the most efficient way to code this trigger?
======================
System: Postgresql 8.3
======================
Code:
----------------------
CREATE OR REPLACE insert_existing() RETURNS trigger AS $$
BEGIN PERFORM 1 FROM zhorse WHERE id = NEW.id; IF NOT FOUND THEN RETURN NEW; ELSE UPDATE tbl SET
(c1,c2, c3, ...) = (NEW.c1, NEW.c2, NEW.c3, ...) WHERE id = NEW.id;
RETURN NULL; END IF; END $$ LANGUAGE plpgsql;
CREATE TRIGGER a_insert_existing BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE insert_existing();