How to convert a PL/PgSQL procedure into a dynamic one?

Question

I am trying to write a plpgsql procedure to perform spatial tiling of a postGIS table. I can perform the operation successfully using the following procedure in which the table names are hardcoded. The procedure loops through the tiles in tile_table and for each tile clips the area_table and inserts it into split_table.

CREATE OR REPLACE PROCEDURE splitbytile()
AS $$
DECLARE
    tile RECORD;
BEGIN
    FOR tile IN 
        SELECT tid, geom FROM test_tiles ORDER BY tid 
    LOOP
        INSERT INTO split_table (id, areaname, ttid, geom)
        SELECT id, areaname, tile.tid,
        CASE WHEN st_within(base.geom, tile.geom) THEN st_multi(base.geom)
        ELSE st_multi(st_intersection(base.geom, tile.geom)) END as geom
        FROM area_table as base
        WHERE st_intersects(base.geom, tile.geom);
        COMMIT;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql';

Having tested this successfully, now I need to convert it to a dynamic procedure where I can provide the table names as parameters. I tried the following partial conversion, using format() for inside of loop:

CREATE OR REPLACE PROCEDURE splitbytile(in_table text, grid_table text, split_table text)
AS $$
DECLARE
    tile RECORD;
BEGIN
    FOR tile IN 
        EXECUTE format('SELECT tid, geom FROM %I ORDER BY tid', grid_table) 
    LOOP
        EXECUTE 
            FORMAT(
                'INSERT INTO %1$I (id, areaname, ttid, geom)
                SELECT id, areaname, tile.tid,
                CASE WHEN st_within(base.geom, tile.geom) THEN st_multi(base.geom)
                ELSE st_multi(st_intersection(base.geom, tile.geom)) END as geom
                FROM %2$I as base
                WHERE st_intersects(base.geom, tile.geom)', split_table, in_table
            );
        COMMIT;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql';

But it throws an error

missing FROM-clause entry for table "tile"

So, how can I convert the procedure to a dynamic one? More specifically, how can I use the record data type (tile) returned by the for loop inside the loop? Note that it works when format is not used.

Answer 1

You can use EXECUTE ... USING to supply parameters to a dynamic query:

EXECUTE
   format(
      'SELECT r FROM %I WHERE c = $1.val',
      table_name
   )
INTO result_var
USING record_var;

The first argument to USING will be used for $1, the second for $2 and so on.

See the documentation for details.