how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL

Question

Here is tbl1 :

gid pm
1   2.1
2   2.0
3   1.95
...

Here is tbl2 I want :

gid num pb  gidTbl1
1    1        1
2    2        1
3    3  2.1   1
4    1        2
5    2        2
6    3        2
7    4  2.0   2
8    1        3
...

On an insert in tbl2, I would like that pb = pm for gidTbl1 = gid AND for the max num in tbl2.
Here are a trigger and the function on tbl2 I wrote :

CREATE OR REPLACE FUNCTION maj_pb()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
IF TG_OP IN ('INSERT')
THEN NEW.pb = pm from tbl1
        where NEW.gidTbl1 = tbl1.gid
        and num = (select max(num) from tbl2
                          where NEW.gidTbl1 = g.gid
                        ) ;
RETURN NEW ;
END IF ;
END ;
$BODY$;
CREATE TRIGGER trg_maj_pb
BEFORE INSERT
ON tbl2
FOR EACH ROW
EXECUTE PROCEDURE activite.maj_pb();

error message is :

num doesn't exist

I presume that the trigger is looking for num in tbl1 (?). But I can't find an other way to write the function.

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Erwin Brandstetter · Answer 1 · 2020-11-27T08:28:32.560

The immediate error is that you have num where it would have to be NEW.num. But there are more problems ...

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Trying to get this right with triggers is a pain, even without possible concurrent writes.

With possible concurrent writes, it's only even possible with expensive locks (or with SERIALIZABLE snapshot isolation). You can't have multiple concurrent transactions messing with the max num at the same time.

You could force all write access on tbl2 to first take out a row lock on the respective parent row in tbl1 (and keep track of the current max tbl2.num in an additional column tbl1.max_num while being at it) ...
See:

But I wouldn't go there. The safe way to achieve this is to drop the column tbl2.pb altogether. Instead use a VIEW (or MATERIALIZED VIEW) like this to dynamically get the current value, where you need it:

CREATE VIEW tbl2_plus_pb AS
SELECT t2.gid, t2.num, t1.pm AS pb, t2.gid_tbl1
FROM  (
   SELECT *, CASE WHEN max(num) OVER (PARTITION BY gid_tbl1) = num
                  THEN gid_tbl1 END AS link
   FROM   tbl2
   ) t2
LEFT   JOIN tbl1 t1 ON t1.gid = t2.link
ORDER  BY t2.gid;

db<>fiddle here

how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL

1 Answers1