Insert/Update/Delete table from another table changes in PostGIS

Question

I'm trying to build a function that Insert/Update/Delete rows of a table based from another table changes. Unfortunately I could not continue, some problems occured:

1. DELETE isn't work correctly: Any linestring cannot deleted when trigger is enabled;
2. INSERT/UPDATE - More than 1 row is returned from query - I insert the 'DISTINCT' to fix - I don't know why;
3. I would like to add point's manually (If possible).

The intention is create, update or delete a point for each line inserted, updated or deleted when this line have a specific atribute called 'con';

These points are generated on the center of each line;

Point table - prec_peca_receive ;

Line table - string

Here is the code:

CREATE OR REPLACE FUNCTION public.insert_peca_by_line()
 RETURNS trigger 
 LANGUAGE plpgsql
 AS $func$
BEGIN
 CASE TG_OP
 WHEN 'INSERT' THEN
 INSERT INTO public.prec_point_receive (geom) 
 select distinct st_lineinterpolatepoint(new.geom, 0.5)::geometry (point,31984)
 from public.string s
 where s.geom is not null and s.str_type = 'con';
 WHEN 'DELETE' THEN
 delete from public.prec_point_receive p
 where st_lineinterpolatepoint(old.geom, 0.5) = p.geom;
 WHEN 'UPDATE' THEN
 UPDATE public.prec_point_receive p
 set (geom) = 
 (
 select distinct st_lineinterpolatepoint(new.geom, 0.5)::geometry (point,31984)
 from string s
 where p.geom is distinct from (st_lineinterpolatepoint(new.geom, 0.5))
 and st_lineinterpolatepoint(old.geom, 0.5) in 
 (select p.geom 
 from prec_point_receive)
 );
 ELSE
 --nothing
 END CASE;
 RETURN NEW;
END;
$func$;
CREATE TRIGGER tg_peca_by_line
 BEFORE INSERT OR DELETE OR UPDATE 
 ON public.string
 FOR EACH ROW
 EXECUTE PROCEDURE public.insert_peca_by_line();

Could someone clarify?

Answer 1

For the insert, you don't need to re-select from the source table.

As it is, you are computing the centroid of the new row geometry (so 1 row) for EVERY row that has a geometry and the desired attribute. Instead, just work on the NEW record

IF NEW.str_type = 'con' THEN
 INSERT INTO public.prec_point_receive (geom) 
 select st_lineinterpolatepoint(new.geom, 0.5)::geometry (point,31984);
END IF;

For the delete and the update, you can do the same. BUT it is a bad idea to work solely on the geometry... what if two intersecting lines share the same centroid?.. both would be deleted. Instead, the proper way is to assign the same ID to your line and point, then finding the row to be updated or deleted would be done solely on the ID.

ex:

delete from public.prec_point_receive p
 where p.ID = OLD.ID