Trying to FOR LOOP a column with ST_CONTAINS in PgAdmin4

Question

I am working with PgAdmin4 to create a View that consists of a large set of geometric data. Part of this data is polylines that exist within polygons. I am attempting to write a code that can loop through all of my polyline data in a given column, and check if it is in a given polygon, and return true/false. So far this is what I have.

DO
$$
BEGIN
    FOR i IN (SELECT "geom" FROM "street map"."segment_id")
    LOOP
        SELECT ST_CONTAINS(
            (SELECT "geom" FROM "street map"."cc_districts" WHERE "district number" = 1),
            (i)
        )
        RETURN NEXT i
    END LOOP;
END
$$  

The error I receive when running this code is as follows:

ERROR:  loop variable of loop over rows must be a record or row variable or list of scalar variables
LINE 4:  FOR i IN (SELECT "geom" FROM "street map"."segment_id")
             ^
SQL state: 42601
Character: 18

From what I understand, "i" must refer to a "row variable", and I tried to define that variable with this piece of code:

(SELECT "geom" FROM "street map"."segment_id")

Any ideas to get this going would be very helpful.

Answer 1

I have found a way to make this work without doing a JOIN or needing a FOR LOOP. The following code works.

SELECT * FROM "street map"."segment_id"
   WHERE
      ST_WITHIN(
          ST_CENTROID((ST_SetSRID(geom, 4326))), 
          ST_SetSRID((SELECT geom FROM "street map"."cc_districts" WHERE "district number" = 1),4326))  

This lets me do what I was intending by runing the process on all rows in a given column. I swapped from ST_CONTAINS to ST_WITHIN, and I also am now checking if the centroid of the polyline is within the given polygon by using ST_CENTROID. I found that the error goes away by asserting the SRID of the geometry to 4326 using ST_SetSRID. I'm not sure why that works, as my geoms already have an SRID of 4326.

Thanks for all of those who answered