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