From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Trigger: update if record exists
Date: Mon, 9 May 2011 13:17:17 +0100
Message-ID: <20110509131717.1370e36f@dick.coachhouse> (raw)
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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql3@numerixtechnology.de
Subject: Re: Trigger: update if record exists
In-Reply-To: <20110509131717.1370e36f@dick.coachhouse>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox