Re: A history procedure that prevents duplicate entries

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