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:
- DELETE isn't work correctly: Any linestring cannot deleted when trigger is enabled;
- INSERT/UPDATE - More than 1 row is returned from query - I insert the 'DISTINCT' to fix - I don't know why;
- 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?