Date: Mon, 9 May 2011 13:17:17 +0100
From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Trigger: update if record exists
Message-ID: <20110509131717.1370e36f@dick.coachhouse>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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();