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