Trigger: update if record exists

From: Tarlika Elisabeth Schmitz <postgresql3(at)numerixtechnology(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Trigger: update if record exists
Date: 2011-05-09 12:17:17
Message-ID: 20110509131717.1370e36f@dick.coachhouse
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Ozer, Pam 2011-05-09 16:25:16 Sorting Issue
Previous Message Pavel Stehule 2011-05-09 10:18:09 Re: FOR EACH STATEMENT trigger ?