Dynamically define a RETURN table (column type, name) for subsequent loop

Question

I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.

https://postgis.net/docs/ST_Intersection.html

https://postgis.net/docs/ST_Intersects.html

POSTGIS intersection does not support the processing of multiple geometries natively, differently from ST_Intersects() and I had to design a function (returning a table) that selects rows intersecting with my clipper_geom using ST_Intersects(), loop through the result set and compute each intersection with ST_Intersection(). The geom field and clipped_geom_wkt are the ones recording the clipped geometry.

The function works, but I need a different one for every table we want to produce the clipping. I would like to read the input table dynamically (column name and type) and define both in the RETURN statement.

All field names and types are the same, only geom is updated and clipped_geom_wkt is added.

I tried to search Stack Overflow and I found examples on how to create dynamic table structure but none performs a subsequent LOOP over the first results, where the column names have to be matched to insert / update the new data.

This is what I have come up so far, but I am unsure about how to perform the LOOP part, to add the clipped_geom_wkt field and update the geom field. Some responses advise to use RETURNS TABLE (...) if more fields are added to SETOF ...

https://stackoverflow.com/questions/17821482/easy-way-to-have-return-type-be-setof-table-plus-additional-fields

But then it seems that dynamically generated columns are only supported with RETURNS SETOF ...

https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557

https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675#8611675

CREATE OR REPLACE FUNCTION clip_palin_polygon_complete(clipped_table text,clipper_geom text, age_sequence VARCHAR)
RETURNS TABLE (rec clipped_table, clipped_geom_wkt text)) AS $$ --not sure if this is the right way to do it...
DECLARE var_r record;
BEGIN
    FOR var_r IN (
        SELECT * FROM clipped_table 
        WHERE clipped_table.seq IN (age_sequence)
        AND ST_Intersects(ST_GeomFromText(clipper_geom,4326), clipped_table.geom)
    )
    LOOP
        /* 
        these are the original table fields that I would like to keep and match
        dynamically with any table I have as input (clipped_table)
        objectid := var_r.objectid;
        seq := var_r.seq;
        -- etc.
        */

        --below there are the only two fields that need modifying
        geom := (
            SELECT ST_Intersection(ST_GeomFromText(clipper_geom, 4326), var_r.geom) AS geom);
        clipped_geom_wkt := (
            SELECT
            ST_AsText(ST_Intersection(ST_GeomFromText(clipper_geom,4326), var_r.geom)) AS clipped_geom_wkt);
        RETURN NEXT;
    END LOOP;
END; $$
LANGUAGE 'plpgsql'

Answer 1

What you might need

You should be able to avoid all the complication with a simple SELECT instead:

SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2
FROM   tbl t  -- replace tbl with actual table
JOIN   ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom)
WHERE  t.seq = $age_sequence;

Minor difference: you get the original geom and the intersection geom2 in the result. Add the redundant ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt to the SELECT list if you really need it.

What you ask

I understand your question like this:

I have various input tables, each has a column geom geometry. I want a function to take a table name and clipper_geom geometry as input and return all rows intersecting with my clipper_geom. One column clipped_geom_wkt text shall be appended and the value of geom changed, each showing the intersection with clipper_geom.

That's not trivial. SQL is a static language, a function demands to know the return type at call time at the latest. We can achieve dynamic results with polymorphic types, but we need a well-known row type to work with. The row type of an existing table is good for it, but you want to append another column. That's not easily possible. The workaround is to return the polymorphic row type, plus an extra column (like you seem to have tried already). Decompose the row type in the function call to arrive at your desired result:

CREATE OR REPLACE FUNCTION my_func(_tbl ANYELEMENT
                                 , _clipper_geom text
                                 , _age_sequence text)
  RETURNS TABLE (tbl ANYELEMENT, clipped_geom_wkt text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(_clipper_geom, 4326);  -- compute once
BEGIN
   FOR tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING _age_sequence, _geom  -- data types must match!
   LOOP          
      tbl := json_populate_record(tbl, json_build_object('geom', ST_Intersection(_geom, tbl.geom)));
      clipped_geom_wkt := ST_AsText(tbl.geom);
      RETURN NEXT;
   END LOOP;
END
$func$

Call (important!):

SELECT (tbl).*, clipped_geom_wkt
FROM my_func(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Works for me in Postgres 10.

Notable features

• Polymorphic types.
  Note how the row variable tbl is defined implicitly in the RETURNS clause, where it copies the input type dynamically (ANYELEMENT). So you are not bound to use SETOF with polymorphic types like you speculated.
  Related (you linked to that one yourself):
  • Refactor a PL/pgSQL function to return the output of various SELECT queries
• FOR-IN-EXECUTE to loop over a dynamic query.
• Assignment of a yet-unknown column with json_populate_record(). See:
  • How to set value of composite variable field using dynamic SQL
• Decomposing composite type in the function call with (tbl).*

Simpler

I stuck to your design above, but it's unnecessary complication for the given example. The extra column clipped_geom_wkt is completely redundant, just derive it from geom in the function call. Then we can use the input type directly:

CREATE OR REPLACE FUNCTION my_func2(_tbl ANYELEMENT
                                  , _clipper_geom text
                                  , _age_sequence text)
  RETURNS SETOF ANYELEMENT
  LANGUAGE plpgsql AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(_clipper_geom, 4326);
BEGIN
   FOR _tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING _age_sequence, _geom  -- data types must match!
   LOOP
      _tbl := json_populate_record(_tbl, json_build_object('geom', ST_Intersection(_geom, _tbl.geom)));
      RETURN NEXT _tbl;
   END LOOP;
END
$func$;

Call (important!):

SELECT *, ST_AsText(geom) AS clipped_geom_wkt  -- if you need the redundant column
FROM   my_func2(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);