postgresql - 在动态查询中使用几何数据类型

Question

我正在使用 PostGIS 在 PostgreSQL 中创建一个函数，我在其中接受表名和点的几何形状，我想在函数内部的查询中使用它们......

CREATE OR REPLACE FUNCTION trial(tbl text[],tag text[],geo geometry)
RETURNS boolean AS
$BODY$
DECLARE i integer;
DECLARE len integer;
DECLARE result boolean;
DECLARE bool boolean;

BEGIN
    result=true;
    SELECT ARRAY_LENGTH(tbl,1) INTO len;
    FOR i IN 1..len LOOP
    EXECUTE format('select st_dwithin(geo::geography,geom::geography,1) from %s where name=any(array[%s]) and st_dwithin(geo::geography,geom::geography,1)=true',tbl[i],tag[i]) into bool;

        IF (bool!=true) THEN
        result=false;
        EXIT;
        END IF;

    END LOOP;
    return result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

这显示错误为

LINE 1: select st_dwithin(geo::geography,geom::geography,1) from grn... 

当我使用

%s for that geometry

我得到下一个错误

ERROR:  syntax error at or near "AA40E9A0E5440F498329474092A40"
LINE 1: select st_dwithin(0101000020E6100000002AA40E9A0E5440F4983294...

我是 PostgreSQL 的初学者...在这种情况下我可以使用什么格式说明符？？？？？

Answer 1

您的函数存在一些问题。

首先，在format()函数中只能拼接标识符和文字值，而需要传递一个geography和一个text[ ]，你的函数的两个参数。这基本上意味着您不能使用 format() 函数，而应该使用 use the USING clause .顺便说一句，这是个好消息，因为 format() 生成一个字符串，而且效率很低(你传入一个 geometry，它被 format 转换为字符串() 然后在服务器中解析回一个 geometry，每次循环迭代一次)。 geo 参数是一个 PL/pgSQL 变量，不会被 EXECUTE 解析，因此它也必须通过 USING 子句提供。

动态查询本身也不是特别好写。逻辑表明，如果至少有 1 行具有 tags 之一，并且距离 geo 不到 1 米，则您要返回 true。有效地编写，该查询将是:

SELECT 1 FROM <table>
WHERE name = ANY(<tags>)
  AND ST_DWithin(<geo>, geom::geography, 1, false)
LIMIT 1;

这显然假设所有表都有一个 name 和 geom 列。如果 geom 是 geography 类型，您可以省略转换。使用 false 进行球体计算，这对于这个短距离来说绰绰有余并且计算速度更快。

连同一些流逻辑改进，您的函数将变为:

CREATE OR REPLACE FUNCTION trial(tbl text[], tag text[], geo <b>geography</b>)
RETURNS boolean AS $BODY$
DECLARE
  t   text;
  res int;
BEGIN
  <b>FOREACH t IN ARRAY tbl LOOP </b>
    EXECUTE 'SELECT 1 FROM ' || quote_ident(t) ||
            '  WHERE name = ANY($1)' ||
            '    AND ST_DWithin($2, geom::geography, 1, false) ' ||
            'LIMIT 1'
        INTO res USING tags, geo;

    <b>IF res IS NULL THEN</b>     -- No value was returned so no nearby points
      <b>RETURN false;</b>
    END IF;
  END LOOP;
  <b>RETURN true;</b>              -- If we get here, all tables have at least 1 match
END;
$BODY$ LANGUAGE plpgsql;

此函数采用表格名称数组，然后确定是否所有表格在指定点的 1 米范围内至少有 1 个点具有任何提供的标签。这对我来说听起来不太有用。返回一组 text，每行给出符合(或不符合)该标准的表的名称，这看起来更有用，甚至可能是 RETURNS TABLE (tbl name, num int, tags text[]) 为每个表格提供 1 米内的点数以及该表格的匹配标签的版本。