3

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.