Search Postgresql Archives

Re: A history procedure that prevents duplicate entries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux