Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I have a big table that often needs specific columns updated for a large number of rows. So right now I have it set up so that there is a 'staging' table that has the update-able columns. Then upon entry into this table a trigger function is called that simultaneously updates the large table. so the Trigger is:

CREATE TRIGGER update_dynamic
    AFTER INSERT
    ON pr.update
    FOR EACH ROW
    EXECUTE PROCEDURE pr.updater();

and the function:

CREATE OR REPLACE FUNCTION pr.updater()
    RETURNS trigger AS
    $BODY$
    BEGIN
        UPDATE pr.large_table plt
        set col_1 = new.col_1,
        col_2 = new.col_2
        where col_1 = new.col_1 and new.col_2 is not null;

        UPDATE pr.large_table plt
        set col_3 = new.col_3
        where col.1 = new.col.1 and new.col.3 is not null;

        UPDATE pr.large_table plt
        set col_4 = new.col_4
        where col_1 = new.col_1 and new.col_4 is not null;

    delete from pr.update 
    where col_1 = new.col_1;

RETURN new;

end;
    $BODY$
     LANGUAGE plpgsql VOLATILE
     COST 100;
     ALTER FUNCTION pr.updater()
     OWNER TO xxxxxxx;

I have alot more columns and thus alot more 'UPDATE pr.large_table plt' conditionals.

Thus my question is how can I make this more efficient? I really don't like running a where and on every Update.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.