为后续循环动态定义返回表(列类型、名称)

Question

我想对表中的行数执行ST_Intersection(clipper_geom, clipped_geom)。

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

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

POSTGIS交集在本质上不支持多个几何学的处理，与ST_Intersects()不同，我不得不设计一个函数(返回一个表)，它使用ST_Intersects()选择与我的clipper_geom相交的行，循环遍历结果集并使用ST_Intersection()计算每个交集。geom字段和clipped_geom_wkt是记录剪裁几何的字段。

这个函数可以工作，但是对于我们想要产生裁剪的每个表，我需要一个不同的函数。我想动态地读取输入表(列名和类型)，并在RETURN语句中定义这两个表。

所有字段名和类型都是相同的，只更新geom和添加clipped_geom_wkt。

我尝试搜索堆栈溢出，并找到了关于如何创建动态表结构的示例，但是没有一个在第一个结果上执行后续的LOOP，在第一个结果中，必须匹配列名才能插入/更新新数据。

这就是我到目前为止提出的内容，但我不确定如何执行LOOP部件、添加clipped_geom_wkt字段和更新geom字段。如果向RETURNS TABLE (...)添加了更多的字段，一些响应建议使用SETOF .

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

但是，似乎动态生成的列只支持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

您可能需要的

您应该能够使用简单的SELECT来避免所有的复杂情况：

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;

细微的区别:得到结果中的原始geom和交集geom2。如果确实需要，将冗余ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt添加到SELECT列表中。

你问

什么

我理解你的问题如下：

我有各种输入表，每个表都有一个列geom geometry__。我想要一个函数以一个表名和clipper_geom geometry作为输入，并返回与我的clipper_geom__相交的所有行。将追加一列clipped_geom_wkt text，并更改geom的值，每一列都显示与clipper_geom__的交集。

这可不是小事。SQL是一种静态语言，函数要求最迟在调用时知道返回类型。我们可以使用多态类型实现动态结果，但我们需要一个众所周知的行类型来处理。现有表的行类型对其有好处，但您希望追加另一列。那可不容易。解决方法是返回多态行类型，外加一个额外的列(就像您已经尝试过的那样)。分解函数调用中的行类型以获得所需的结果：

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$

打电话(重要！)：

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

在Postgres 10为我工作。

显著特性

• 多态类型注意行变量tbl是如何在RETURNS子句中隐式定义的，其中它动态地复制输入类型(ANYELEMENT)。因此，您不必像推测的那样使用多态类型的SETOF。关联(您自己链接到该链接)：
  • 重构PL/pgSQL函数以返回各种选择查询的输出

• FOR-IN-EXECUTE循环处理动态查询。
• 用json_populate_record()分配一个未知的列。请参阅：
  • 如何使用动态SQL设置复合变量字段的值

• 用(tbl).*分解函数调用中的复合类型

简单

我坚持上面的设计，但是对于给定的例子来说，这是不必要的复杂。额外的列clipped_geom_wkt是完全冗余的，只需在函数调用中从geom派生它。然后我们可以直接使用输入类型：

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$;

打电话(重要！)：

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);