How to loop through a string array and create multiple tables in Postgresql

Question

I would like to take a large table and break in up into smaller ones. I have the following code snippet which works by manually replacing "NAME" with the unique name in ui00000bvbb.lad15nm:

   CREATE TABLE "NAME" AS
   SELECT parcels_all_shapefile.* AS parcels
   FROM ui00000bvbb INNER JOIN parcels_all_shapefile ON ST_Intersects(ui00000bvbb.wkb_geometry, parcels_all_shapefile.wkb_geometry)
   WHERE ui00000bvbb.lad15nm = "NAME")

My question is how do I loop through a list of names and populate the above code? I have tried the following, but it doesn't work:

DO
$do$
DECLARE
   m   varchar[];
   arr varchar[] := array[['Barnet'],['Westminster']];
BEGIN
   FOREACH m SLICE 1 IN ARRAY arr
   LOOP
       CREATE TABLE m AS
       SELECT parcels_all_shapefile.* AS parcels
       FROM ui00000bvbb INNER JOIN parcels_all_shapefile ON ST_Intersects(ui00000bvbb.wkb_geometry, parcels_all_shapefile.wkb_geometry)
       WHERE ui00000bvbb.lad15nm = m)
   END LOOP;
END
$do$

klin · Accepted Answer · 2018-06-25 12:01:28Z

13

The loop variable should be just text. Use simple FOREACH loop (without SLICE) and dynamic SQL EXECUTE inside the loop:

DO
$do$
DECLARE
    m   text;
    arr text[] := array['Barnet','Westminster'];
BEGIN
   FOREACH m IN ARRAY arr
   LOOP
        EXECUTE format($fmt$
            CREATE TABLE %1$I AS
            SELECT parcels_all_shapefile.* AS parcels
            FROM ui00000bvbb INNER JOIN parcels_all_shapefile ON ST_Intersects(ui00000bvbb.wkb_geometry, parcels_all_shapefile.wkb_geometry)
            WHERE ui00000bvbb.lad15nm = %1$L
        $fmt$, m);
   END LOOP;
END
$do$

1 Comment

user330315 Over a year ago

%1$I should be %I and %1$L should be %L shouldn't it?

Collectives™ on Stack Overflow

How to loop through a string array and create multiple tables in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related