I have a postgres table in the form of

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)

CodePudding user response：

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

CodePudding user response：

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.