PostgreSQL 函数遍历查询，如何返回表

Question

我有一个包含 ID、时间和 PostGIS 点对象列的表。该表包含每个 ID 在不同时间的多个点。我想找到一个 ID 的点集到另一个 ID 的点集的平均距离，对于同时发生的每个点，并找到所有其他 ID 的点集与原始 ID 的点集相比的平均值。

到目前为止我有这个功能:

CREATE TYPE score AS (id int, dist float);
CREATE OR REPLACE FUNCTION avgdist(id1 int) RETURNS TABLE (id int, dist float) LANGUAGE plpgsql AS 
$func$ 
    DECLARE 
    scores score; 
    id2 int; 
    set2 record; 
    begin 
        id2:= 0; 
        IF (id1 = id2 ) THEN 
            id2:= 1; 
        END IF; 
        FOR set2 IN 
        SELECT my_table.id, my_table.time, my_table.geom FROM my_table WHERE my_table.id = id2 loop 
            id2:= id2 + 1; 
            CONTINUE WHEN id1 = id2; 
            EXECUTE 'WITH origin AS (SELECT time, id, geom FROM my_table WHERE id = $1)
                SELECT id, avg(ST_Distance(origin.geom, $2)) 
                FROM origin WHERE origin.time = $3 
                group by origin.id 
                ORDER BY id' 
                    INTO scores 
                    USING id1, set2.geom, set2.time; 
        end loop; 
        RETURN; 
    end 
$func$;

当使用 select * from avgdist(2) 调用此函数时，我没有得到任何结果，与 slect avgdist(2) 相同。当我在插入值的 psql 中自行运行查询时，我确实得到了结果。

我不熟悉在 sql 中创建函数，所以我真的不明白当该表没有名称时如何将分数放入返回表中。而且我似乎无法使用 RETURN QUERY，因为我需要返回循环中所有查询的结果。

在此方面的帮助将不胜感激，或者是否有更好的方法可以在不循环的情况下实现我想要的结果？

Answer 1

还有更多问题:

• 子句 RETURNS TABLE 定义了 OUT 变量。您可以使用这些变量。

  CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (r1 int, r2 int)
  -- you don't need aux variables for result
  ...
    r1 := 10; r2 := 10;
    RETURN NEXT;
  

• EXECUTE INTO 只能存储动态查询结果的第一行(或值)。

• RETURN 停止函数计算。您应该使用 RETURN NEXT 或 RETURN QUERY。

  FOR x, y IN SELECT ..
  LOOP
    -- when only first row of result is interesting
    EXECUTE '..' INTO r1, r2 USING x, y;
    RETURN NEXT;
  END LOOP
  

  或使用RETURN QUERY EXECUTE

  FOR x, y IN SELECT ..
  LOOP
    RETURN QUERY EXECUTE '..' USING x, y;
  END LOOP
  

• 我不理解您的代码，但看起来您不需要使用动态 SQL - EXECUTE 命令。仅当您使用变量代替 SQL 标识符时才需要动态 SQL。没有这种情况。同样的情况 - 为什么你使用 CTE WITH 子句。这不是必需的 - 它可能会产生负面影响(不需要的物化 - 由 PostgreSQL 12 修复)。仅使用 RETURN QUERY(不使用 EXECUTE)并将查询作为查询(而非字符串)传递。

关于这些命令的文档很好 - https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING