Make array_append in trigger with PostGIS intersection

Question

I've 2 layers : linestring with column id_line and polygone, drawn by user. I trying to get id_line on linestring who intersects polygon. I've got it in qgis with :

aggregate(
layer:= 'aep_cana',
aggregate:='concatenate',
expression:=id_cana,
concatenator:='; ',
filter:=intersects($geometry, geometry(@parent)))

in default value, It's not really an array, more like a list but it's ok for me.

So I try to make the same thing when I create a polygon, automatically, in my database with a trigger (because in QGIS, users can do anything they want).

CREATE OR REPLACE
FUNCTION asst.transf_id_line() RETURNS TRIGGER LANGUAGE plpgsql AS $function$ 
BEGIN
    UPDATE asst.test_emprise SET
    list = array_append(list, (SELECT c.id_line FROM line."id_line" AS c WHERE st_within(NEW.geom, c.geom))) ;
END;
$function$
;

Doesn't work, I write this code just for idea. But I try some simple array_append in trigger, but it makes loop...

So I find WITH RECURSIVE like in Postgis: aggregate function for intersection but I can't apply it to me. It this a good way or not? How can I use array_append in a trigger?

---

After 2 hours, I can make

WITH RECURSIVE idsel AS (
SELECT
    te.id AS idi,
    ARRAY_AGG( c.id_line::TEXT) AS aa
FROM
    asst.test_emprise AS te
CROSS JOIN asst."line" AS c
WHERE
    ST_INTERSECTS(c.the_geom,te.geom)
GROUP BY
    te.id )
--SELECT * FROM idsel;
UPDATE asst.test_emprise AS op SET list = idsel.aa FROM idsel WHERE idsel.idi = op.id;

But I can't transform in trigger.

Answer 1

There are several things wrong in the original trigger function:
1) the same table as the target table is used in an update statement (likely creating the loop you are mentioning)
2) the entire table is updated, not just the new record.
3) don't use array_append but rather array_agg, to avoid repeating all the values in case of a polygon update
4) there is no return statement.

Here is a possible solution, assuming it is a before trigger

CREATE OR REPLACE
FUNCTION asst.transf_id_line() RETURNS TRIGGER LANGUAGE plpgsql AS $function$ 
BEGIN
    NEW.list = SELECT array_agg(c.id_line) 
               FROM line."id_line" AS c 
               WHERE st_intersects(NEW.geom, c.geom) ;
    RETURN NEW;
END;
$function$
;