Using 'For each' type loop in PostgreSQL?

Question

I'm really struggling with optimizing one of my sources. Instead of doing all the request and foreach loops in my server side application. I am thinking about shifting it fully to the database side. But I am struggling!

SELECT ST_asText( ST_ConcaveHull(ST_Collect(the_geom), 0.95)) FROM ways
JOIN
(SELECT seq, id1 AS node, cost
        FROM pgr_drivingDistance(
                'SELECT gid as id, source, target, reverse_cost as cost FROM ways',
                pgr_pointtoid(ST_setSRID(ST_MakePoint(7.26, 46.57),4326), 0.01)::Integer
, 10, false, false)) as dd_route
 ON
 ways.gid = dd_route.node;

But now I would like to replace the static point with a list or an array, and I just can't figure out how. I know there are a few cracks in PostgreSQL on this site.

We could use the following query to gather the data:

SELECT ST_SetSRID(ST_MakePoint(tmp[1]::double precision, tmp[2]::double precision), 4326) FROM (
    SELECT string_to_array(unnest(content), ',') as tmp FROM (
        SELECT string_to_array('7.26,46.57/8.26,46.25', '/') as content
    ) as data_array
) as data_row

The idea is now to the static set ST_MakePoint statement with a 'kind of foreach' statement.

Answer 1

Generally (not always) if you are reaching for a procedural style in SQL you might be looking for the wrong thing.

If you have all the points you need to calculate driving directions to you could do something like this:

 (SELECT seq, id1 AS node, cost
    FROM (SELECT pgr_drivingDistance(
            'SELECT gid as id, source, target, reverse_cost as cost FROM ways',
            pgr_pointtoid(points.geom, 0.01)::Integer
  , 10, false, false) FROM points)
 ) as dd_route

So you have a sub query:

   SELECT pgr_drivingDistance(
                'SELECT gid as id, source, target, reverse_cost as cost FROM ways',
                pgr_pointtoid(points.geom, 0.01)::Integer
      , 10, false, false) FROM points

Which returns the drivingDistance for each point in the points table

Answer 2

You can use CROSS JOIN LATERAL to make your life easier and queries cleaner.

SELECT s, ST_AsText(
  ST_SetSRID(
    ST_MakePoint(xy[1]::double precision,xy[2]::double precision),
    4326
  )::geography
)
FROM ( VALUES
  ('7.26,46.57/8.26,46.25')
) as data_row(s)
CROSS JOIN LATERAL unnest(string_to_array(data_row.s, '/')) AS cords
CROSS JOIN LATERAL string_to_array(cords, ',') AS xy;

returns

           s           |     st_astext     
-----------------------+-------------------
 7.26,46.57/8.26,46.25 | POINT(7.26 46.57)
 7.26,46.57/8.26,46.25 | POINT(8.26 46.25)
(2 rows)

You can just as easy move things out of the sub-select like this. (returns the same thing)

SELECT s, ST_AsText(geog)
FROM ( VALUES
  ('7.26,46.57/8.26,46.25')
) as data_row(s)
CROSS JOIN LATERAL unnest(string_to_array(data_row.s, '/')) AS cords
CROSS JOIN LATERAL string_to_array(cords, ',') AS xy
CROSS JOIN LATERAL CAST(ST_SetSRID(
  ST_MakePoint(xy[1]::double precision,xy[2]::double precision),
  4326
) AS geography) AS geog;