Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS

So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as it is being entered. Then, I was going use this geometry to grab the nearest structure (a point) and autocomplete two columns with corresponding data. I have working code that gets me the endpoints of existing pipes and finds the nearest structures, but I want to use the newly created lines (the pipes) to complete the form automatically as it is being entered into the database.

Working Code:

 SELECT p.id,
    dump_line.geom,
    st_endpoint(dump_line.geom) AS downstream,
    j.geom_closest_downstream,
    st_startpoint(dump_line.geom) AS upstream,
    i.geom_closest_upstream
   FROM sewers.pipes p,
    LATERAL st_dump(st_setsrid(p.geom, 2965)) dump_line(path, geom),
    LATERAL ( SELECT s.geom
           FROM sewers.structures s
          ORDER BY (st_endpoint(dump_line.geom) <-> s.geom)
         LIMIT 1) j(geom_closest_downstream),
    LATERAL ( SELECT s.geom
           FROM sewers.structures s
          ORDER BY (st_startpoint(dump_line.geom) <-> s.geom)
         LIMIT 1) i(geom_closest_upstream);

I need to figure out how to get data from an update (adding an entry to qgis) and use its geometry instead of the geometry of existing pipes.

Answer

Just put this query into a function ..

CREATE OR REPLACE FUNCTION insert_pipe() RETURNS TRIGGER AS $$
BEGIN
  SELECT 
  j.geom_closest_downstream,
  i.geom_closest_upstream
  INTO NEW.geom_closest_downstream, NEW.geom_closest_upstream
  FROM ST_Dump(NEW.geom) dump_line,
  LATERAL (SELECT s.geom 
           FROM structures s
           ORDER BY ST_EndPoint((dump_line).geom)<->s.geom 
           LIMIT 1) j (geom_closest_downstream),
  LATERAL (SELECT s.geom 
           FROM structures s
           ORDER BY ST_StartPoint((dump_line).geom)<->s.geom 
           LIMIT 1) i (geom_closest_upstream);
           
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

And attach a trigger to it, e.g. a BEFORE INSERT OR UPDATE trigger:

CREATE TRIGGER t_insert_pipe 
BEFORE INSERT OR UPDATE ON pipes FOR EACH ROW EXECUTE PROCEDURE insert_pipe();

This demo might give you some hints: db<>fiddle