A history procedure that prevents duplicate entries
Hi all,
I've been using a procedure to make a copy of data in my public
schema into a history schema on UPDATE and INSERTs.
To prevent duplicate entries in the history, I have to lead in the
current data, compare it in my program and then decided whether
something has actually changed or not before doing an update. This
strikes me as wasteful coding and something I should be able to do in my
procedure.
Given the following example tables and procedure, how could I go
about changing it to prevent duplicate/unchanged entries being saved to
the history schema? Even a pointer to a relevant section of the docs
would be appreciated... My knowledge of procedures is pretty weak. :)
Madi
CREATE TABLE radical
(
rad_id integer primary key default(nextval('id_seq')),
rad_char text not null,
rad_name text
);
CREATE TABLE history.radical
(
rad_id integer not null,
rad_char text not null,
rad_name text,
hist_id integer not null
default(nextval('hist_seq')),
modified_date timestamp default now()
);
CREATE FUNCTION history_radical() RETURNS "trigger"
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);
RETURN NULL;
END;$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR EACH
ROW EXECUTE PROCEDURE history_radical();
On 16 Aug 2009, at 4:24, Madison Kelly wrote:
Hi all,
...
CREATE FUNCTION history_radical() RETURNS "trigger"
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE
rad_id=new.rad_id;
I assume you mean to only select an existing record here in case the
trigger is fired on an update? You are in fact always selecting at
least one record here because this is called from an AFTER INSERT OR
UPDATE trigger; the record has already been inserted or updated, so
the select statement will find the new (version of) the record.
I'm also not entirely sure what the value is of calling your procedure
on INSERT. If I interpreted you correctly the same data would be added
to the history the first time it gets updated (except for the
different timestamp and history id of course). I'd probably just call
this procedure on UPDATE, and on DELETE too. If you do want to fire on
INSERT I'd make it clear there was no data before that history entry,
for example by filling the record with NULL values or by adding a
column for the value of TG_OP to the history table.
Besides that, you don't need the SELECT statement or the RECORD-type
variable as the data you need is already in the NEW and OLD records.
But, you only have an OLD record when your trigger fired from an
UPDATE, so you need to check whether your trigger fired from INSERT or
UPDATE.
So, what you need is something like:
IF TG_OP = 'INSERT' THEN
hist_radical := NEW;
ELSE -- TG_OP = 'UPDATE'
hist_radical := OLD;
END IF;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char,
hist_radical.rad_name);
RETURN NULL;
END;$$
LANGUAGE plpgsql;CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR
EACH ROW EXECUTE PROCEDURE history_radical();
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a87e8d010131556343596!
Hi Madi,
I think you want to use foreign keys which can give you these checks. So
add a foreign key to create a link between rad_id of both tables.
regards,
Bastiaan
Madison Kelly wrote:
Show quoted text
Hi all,
I've been using a procedure to make a copy of data in my public
schema into a history schema on UPDATE and INSERTs.To prevent duplicate entries in the history, I have to lead in the
current data, compare it in my program and then decided whether
something has actually changed or not before doing an update. This
strikes me as wasteful coding and something I should be able to do in
my procedure.Given the following example tables and procedure, how could I go
about changing it to prevent duplicate/unchanged entries being saved
to the history schema? Even a pointer to a relevant section of the
docs would be appreciated... My knowledge of procedures is pretty
weak. :)Madi
CREATE TABLE radical
(
rad_id integer primary key
default(nextval('id_seq')),
rad_char text not null,
rad_name text
);CREATE TABLE history.radical
(
rad_id integer not null,
rad_char text not null,
rad_name text,
hist_id integer not null
default(nextval('hist_seq')),
modified_date timestamp default now()
);CREATE FUNCTION history_radical() RETURNS "trigger"
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE
rad_id=new.rad_id;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char,
hist_radical.rad_name);
RETURN NULL;
END;$$
LANGUAGE plpgsql;CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR
EACH ROW EXECUTE PROCEDURE history_radical();
Alban Hertroys wrote:
On 16 Aug 2009, at 4:24, Madison Kelly wrote:
Hi all,
...
CREATE FUNCTION history_radical() RETURNS "trigger"
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE
rad_id=new.rad_id;I assume you mean to only select an existing record here in case the
trigger is fired on an update? You are in fact always selecting at least
one record here because this is called from an AFTER INSERT OR UPDATE
trigger; the record has already been inserted or updated, so the select
statement will find the new (version of) the record.I'm also not entirely sure what the value is of calling your procedure
on INSERT. If I interpreted you correctly the same data would be added
to the history the first time it gets updated (except for the different
timestamp and history id of course). I'd probably just call this
procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT
I'd make it clear there was no data before that history entry, for
example by filling the record with NULL values or by adding a column for
the value of TG_OP to the history table.
The INSERT is there mainly for my convenience. If I am going to the
history schema to get data, it's convenient to know that is has a
complete copy of the data in the public schema, too.
Besides that, you don't need the SELECT statement or the RECORD-type
variable as the data you need is already in the NEW and OLD records.
But, you only have an OLD record when your trigger fired from an UPDATE,
so you need to check whether your trigger fired from INSERT or UPDATE.So, what you need is something like:
IF TG_OP = 'INSERT' THEN
hist_radical := NEW;
ELSE -- TG_OP = 'UPDATE'
hist_radical := OLD;
END IF;INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);Alban Hertroys
To help me improve my understanding of procedures, how would this
prevent an UPDATE from creating a new entry in the history schema when
all the column values are the same as the last entry in history?
Thanks!!
Madi
On 16 Aug 2009, at 17:38, Madison Kelly wrote:
Besides that, you don't need the SELECT statement or the RECORD-
type variable as the data you need is already in the NEW and OLD
records. But, you only have an OLD record when your trigger fired
from an UPDATE, so you need to check whether your trigger fired
from INSERT or UPDATE.
So, what you need is something like:
IF TG_OP = 'INSERT' THEN hist_radical := NEW;
ELSE -- TG_OP = 'UPDATE'
hist_radical := OLD;
END IF;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char,
hist_radical.rad_name);To help me improve my understanding of procedures, how would this
prevent an UPDATE from creating a new entry in the history schema
when all the column values are the same as the last entry in history?
It doesn't, as it wasn't entirely clear to me how you wanted it to
behave.
To prevent duplicate history entries from updates you would need to
compare the values of NEW and OLD and return if they're equal. In 8.4
that's as simple as checking that NEW IS DISTINCT FROM OLD, but in
earlier versions it's a bit more involved. There was a discussion
about this very topic here recently.
Alban Hertroys
--
Screwing up is the correct approach to attaching something to the
ceiling.
!DSPAM:737,4a8bd41d10131434511488!