[GIS] Aggregate functions not allowed in trigger

postgresqltrigger

I need help with a postgres/postgis trigger.

I have a table of retaining walls, each of which is associated with the adjacent road, except where the wall abuts a junction, where it will be split and associated with the adjacent road. However it retains the same ID & name. Each wall polygon has a width of 1m and so its length equals its area. I have a field in the table which records the area/length and a working trigger to populate it.
What I need is for another trigger to sum the area fields of all of the relevant records and write it to a different table.

The code from the trigger that produces the aggregate functions error is

g:=NEW.gid;
  UPDATE roads.rds_retaining_wall_register a
SET length = sum(b.poly_area)
FROM gazetteer.lsg_special_designations b
WHERE a.refno = b.refno
AND b.gid=g;

After searching for the answer, I think I need to use a select statement to do the sum and then use that to do the update, but being new to postgres, I'm stuck with the syntax of how to do it.

Best Answer

It can be done in a single update, you just need to do some subqueries.

UPDATE roads.rds_retaining_wall_register a
SET length = c.sarea
FROM (
    SELECT refno, SUM(poly_area) sArea 
    FROM gazetteer.lsg_special_designations b
    WHERE refno = (SELECT refno FROM gazetteer.lsg_special_designations WHERE gid = g)
    GROUP BY b.refno
      ) c
WHERE c.refno = a.refno;

Another way would be to use a CTE to make it a bit more readable.

WITH reference AS ( -- Determine the refno we want to update
    SELECT refno FROM gazetteer.lsg_special_designations WHERE gid = g
    ),
    sumarea AS ( -- Sum the area for that refno
    SELECT SUM(poly_area) sumarea
    FROM wallReg w INNER JOIN gazetteer.lsg_special_designations r ON w.refno = r.refno
    )
UPDATE roads.rds_retaining_wall_register a
SET length = sumarea
FROM reference r, sumarea s
WHERE r.refno = a.refno; -- Restrict the update to the refno
Related Question