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.
Another way would be to use a CTE to make it a bit more readable.