Calculating list of existing polygons with distances from newly created QGIS/PostGIS

Question

I have two polygon tables (SRID=4326) in postgres: t_areas and t_sites. Once I insert through QGIS 3,8 a new area in t_areas I'd like PostGIS (by a trigger) to list all sites within a certain km range from new area and insert this list to a memo field of the t_area table, for ex.: sitename01 -> 0,3 km, sitename02 -> 0,12 km... etc. The distance to be calculated between borders, not centroids.

I've managed to compose a query on existing data like:

 select array_to_string (array ( SELECT t_sites.shortname || ' -> ' ||
 round(st_distance(geography (t_areas.geom), geography
 (t_sites.geom)))/1000 || ' km' AS distance FROM t_areas, t_sites WHERE
 t_areas.id_area=133 and round(st_distance(geography (t_sites.geom),
 geography (t_areas.geom)))/1000 < 5 ORDER BY distance ASC), ', ')

and even got result like this:

 Ванино Поле -> 0.661 km, Голубково 1 -> 3.636 km, Голубково 2 -> 4.03
 km, Голубково 3 -> 3.661 km, Задубье 2 -> 0.005 km, Заорешье 1 ->
 4.582 km, Заорешье 2 -> 4.631 km, Калгановка -> 4.385 km, Кут 2 -> 1.526 km, Наволок 1 -> 3.359 km, Петровская Горка 1 - and so on...

but I can't realize how to configure a function and a trigger to force PostGIS then automatically update the t_areas.memo field since a new area serial id_area hasn't been generated yet and so I can't point the function to that id (unlike in the query above).

Answer 1

The new (or modified) row is available in the trigger using the NEW record. You can directly use its geometry or any other attributes to query the 2nd table. The output of the query is then saved in the NEW record, and when done the entire record is commited.

CREATE OR REPLACE FUNCTION public.tg_f() RETURNS trigger AS
    $BODY$
    BEGIN

        SELECT array_to_string(
            array ( 
                SELECT t_sites.shortname || 
                    ' -> ' ||  
                    round(st_distance(NEW.geom::geography, t_sites.geom::geography))/1000 || ' km' AS distance 
                FROM t_sites 
                WHERE round(
                    st_distance(t_sites.geom::geography, NEW.geom::geography))/1000 < 5 
                ORDER BY distance ASC), ', ')
        INTO new.memo;


        RETURN NEW; -- return the updated row
    END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER tg BEFORE INSERT OR UPDATE
   ON  public.t_areas FOR EACH ROW
   EXECUTE PROCEDURE  public.tg_f();