postgresql - 无法执行plpgsql/postgres中的功能

我想用plpgsql计算从地址点到50米范围内所有街道的距离。我尝试了以下功能：

Create or Replace Function get_dist(ad geometry, st geometry)
Returns double precision AS
$$
Begin
Insert into street(Distance)
Select ST_Distance(ad.geom, st.geom) from ad
Left Join st ON ST_DWithin(ad.geom, st.geom, 50.0);
Return ST_Distance(ad.geom, st.geom);
End
$$
Language plpgsql volatile;

创建函数不会出错，但当我尝试使用以下命令调用它时：

Select get_dist(ad.geom, st.geom) from ad
Left Join st ON st.gid = ad.gid;

我得到这个错误：

ERROR:  missing FROM-clause entry for table "ad"
LINE 1: SELECT ST_Distance(ad.geom, st.geom)

有人能强调一下函数有什么问题吗（创建函数并调用它）？

最佳答案

要处理单行或逐个处理行，可以将RETURNING的SQLINSERT子句与plpgsqlINTO子句结合使用。例子：
Get default serial value after INSERT inside PL/pgSQL
但很明显你想一次处理一整套。
计算从地址点到50米范围内所有街道的距离。。。
使用基于集合的方法。更快更干净。如果要从INSERT返回行，请另外使用set returning函数。例子：
Return a query from a function?
你根本不需要函数。仅此查询：

INSERT INTO street(ad_geom, st_geom, distance, traffic_ct)  -- any columns in street
SELECT ad.geom, st.geom, ST_Distance(ad.geom, st.geom), ad.traffic_ct
FROM   ad
LEFT   JOIN st ON ST_DWithin(ad.geom, st.geom, 50.0)
RETURNING *  -- all columns in street

我想您实际上不再需要返回任何内容，因为这个查询满足您的所有要求，但我保留了RETURNING作为概念证明。你可以跳过它。
如果不想包含没有匹配街道的地址，请使用[INNER] JOIN而不是LEFT [OUTER] JOIN。
The manual about RETURNING in INSERT:
可选的RETURNING子句导致INSERT计算并返回
基于实际插入的每行的值[…]任何表达式
允许使用表的列。RETURNING的语法
列表与SELECT的输出列表相同。仅行
将返回已成功插入或更新的。
如果需要将其包装成plpgsql函数（也可以是简单的SQL函数），并且仍然返回所有行：

CREATE OR REPLACE FUNCTION insert_neighbours()
  RETURNS SETOF street AS
$func$
BEGIN
   RETURN QUERY
   INSERT INTO street(ad_geom, st_geom, distance, traffic_ct)  -- any columns in street
   SELECT ad.geom, st.geom, ST_Distance(ad.geom, st.geom), ad.traffic_ct
   FROM   ad
   LEFT   JOIN st ON ST_DWithin(ad.geom, st.geom, 50.0)
   RETURNING *;  -- all columns in street
END
$func$  LANGUAGE plpgsql;

呼叫：

SELECT * FROM insert_neighbours();  -- use SELECT * FROM ... !

为了简单起见，我返回整行，但您也可以返回select列。See above example.
创建函数不会出错
这是因为PL/pgSQL目前只对CREATE FUNCTION运行表面语法检查。您必须实际执行该函数来测试它，并确保plpgsql函数中的所有代码分支都得到测试。

关于postgresql - 无法执行plpgsql/postgres中的功能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37918442/