A history procedure that prevents duplicate entries

[Madison Kelly <linux@xxxxxxxxxxx>]

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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general