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.