I would like to execute
ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.
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
ST_Intersects(), loop through the result set and compute each intersection with
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
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
But then it seems that dynamically generated columns are only supported with
RETURNS SETOF ...
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'