Question 114909 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres rule or trigger to update multiple rows after update of single row

I have a postgres table in the form of

id x_loc
1000 12.7
1500 13.2
1001 12.7
1502 13.2
1002 12.8

and want to make a rule or trigger that will update the x_loc column for all rows that meet a certain criteria whenever I update a single row’s x_loc value.

In my case the first 3 digits of the ‘id’ column can be read as being a group that should always have the same x_loc value in the table. So if I update the x_loc for id 1000, it should also update the x_loc for 1001 and 1002 to the same value. If I update the x_loc for 1502, it should set the x_loc for 1500 to the same value.

I realize that this means the x_loc is duplicated for a lot of entries, and that a better practice might be to separate x_loc into a different table and create a new ‘id’ column that just uses the first 3 digits, but I think that’s going to complicate things with other queries and the rest of the database more than I want to deal with.

I tried the follow RULE but get the infinite recursion detected error for obvious reasons. Is there any way to go about this with rules/triggers? Thanks

CREATE OR REPLACE RULE x_loc_update AS on UPDATE TO tbl_test
WHERE NEW.x_loc<>OLD.x_loc
DO INSTEAD
UPDATE tbl_test SET
x_loc=NEW.x_loc
WHERE left(id,3)=left(NEW.id,3)

Tags:

2

Answers


  1. 0

    Use an AFTER trigger:

    create table loc_tbl (id integer, x_loc numeric);
insert into loc_tbl values (1000,12.7), (1500,13.2), (1001,12.7), (1502,13.2), (1002,12.8);

CREATE OR REPLACE FUNCTION public.x_loc_fnc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.x_loc != OLD.x_loc THEN
        UPDATE 
            loc_tbl
        SET 
            x_loc = NEW.x_loc
        WHERE 
            left(NEW.id::varchar, 3) = left(id::varchar, 3);
    END IF;
RETURN null;
END;

$function$
;

CREATE TRIGGER 
    loc_trg 
AFTER UPDATE ON 
    loc_tbl 
FOR EACH ROW EXECUTE FUNCTION 
    x_loc_fnc();

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1000 |  12.7
 1500 |  13.2
 1001 |  12.7
 1502 |  13.2
 1002 |  12.8

UPDATE loc_tbl SET x_loc = 12.9 WHERE id = 1000;
UPDATE 1

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1500 |  13.2
 1502 |  13.2
 1001 |  12.9
 1002 |  12.9
 1000 |  12.9
    Login or Signup to reply.
  2. 0

    you could use a function here
    and update your ids,
    if you think it can be duplicated, then use lead and add +1 with it for the others.
    I hope it will work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search