Accessing each value from array and feed it to another function in PostGIS?

Question

I have an edge_table containing start_id and start_geom column. I want to make one function which will take integer array as input (starts). In between the function, the array values will be checked with the start_ids of edge_table. If matches, then corresponding start_geom value will be send to st_makeline function. I have made following query but it is having syntax error near 'IN'.

CREATE OR REPLACE FUNCTION ids_to_linegeom (starts int[])
RETURNS geometry AS $BODY$
DECLARE 
  i integer;
  res geometry;
BEGIN
 select st_makeline(geom) from (
    FOREACH i IN ARRAY $1
    LOOP
        SELECT start_geom AS geom
        FROM edge_table
        WHERE start_id = starts[i];
    END LOOP;
      )into res;
   RETURN res;
END;
$BODY$ LANGUAGE plpgsql;

Example of edge_table:

start_id   start_geom                                 
81608    01010000007368912D8B622341E5D022EBEAF65A41         
81557    010100000085EB51F89C0723418B6CE7DB9F8E5A41                  
189898   01010000006F1283C0A093214179E926F1A1005B41        
80952    0101000000666666E67F832341F2D24DBA38B45A41

I have postgresql 9.6.

Answer 1

You can make use of the Postgres unnest function which converts an array into a set of rows, thereby avoiding the whole need to do any For loops.

CREATE OR REPLACE FUNCTION ids_to_linegeom (starts int[]) RETURNS geometry AS $BODY$
DECLARE res geometry;
BEGIN 
   SELECT 
     ST_MakeLine(
       ARRAY(
           SELECT start_geom 
             FROM edge_table 
            WHERE start_id IN (SELECT unnest($1)))) 
             INTO res;
   RETURN res;
END;
$BODY$
LANGUAGE plpgsql;

If your starts int array comes from another SELECT query, it would be possible to rewrite this as a query with an INNER or LEFT JOIN between the edge_table and the table where you are selecting the starts from and do away with the function entirely.