sql - PL/PGSQL 总是返回数组或数组列表

Question

给定简单的 pl/pgsql 函数:

CREATE OR REPLACE FUNCTION foo (point geometry
                              , OUT _street text
                              , OUT _gid int
                              , OUT distance real)
AS $$
BEGIN 

    SELECT min(distance(point,geom)) as dist, gid, name into distance, _gid, _street
    from streets 
    where geometria && Expand(point,0.001) group by gid, name order by dist limit 1;

END;
$$ LANGUAGE plpgsql;

结果类似于:

 geobase=# select foo(GeomFromText('POINT(-99.124191496999 19.3490666368031)',4326));
                      foo                      
 -------------------------------------------------
 ("PASEO DE LOS FRAMBOYANES",345483,0.000118338)

这很好，除了我期待更类似于此的事实:

          _street         |  _gid  |  distance     
--------------------------+--------+-------------
 PASEO DE LOS FRAMBOYANES | 345483 | 0.000118338

我尝试过使用 RETURN 子句的变体，将其定义为行类型、记录甚至表，但我总是得到示例中所示的元组或数组。关于如何以类似于表格的方式获得结果的任何线索？

Answer 1

这个问题已经回答了很多次了。分解返回的行类型:

SELECT * FROM foo( ... );

喜欢这里:

• How to get individual columns from table returned from a function?

---

但还有更多。

您的函数具有更少困惑的语法、更少出错和简化:

CREATE OR REPLACE FUNCTION foo (_point geometry)
  RETURNS TABLE (street text, gid int, distance real)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETRUN QUERY 
   SELECT s.name, s.gid, distance(_point, s.geom)
   FROM   streets s
   WHERE  s.geometria && expand(_point, 0.001)
   ORDER  BY 3
   LIMIT  1;
END
$func$;

既然你有ORDER BY ... LIMIT 1 , 你不需要 min()和 GROUP BY完全没有。

请注意我如何对所有列进行表限定以避免命名冲突。

有一个一个细微差别:如果查询发现没有行，您的原始查询返回一个带有 NULL 的行值，而我的函数实际上不返回任何行。通常，您会想要后者。

与等效的简单 SQL 函数相同:

CREATE OR REPLACE FUNCTION foo (_point geometry)
  RETURNS TABLE (street text, gid int, distance real)
  LANGUAGE sql AS
$func$
SELECT s.name, s.gid, distance(_point, s.geom)
FROM   streets s
WHERE  s.geometria && expand(_point, 0.001)
ORDER  BY 3
LIMIT  1;
$func$;

对于大表仍然不是很有效。您会对 PostGis 中的 KNN(k 最近邻)搜索感兴趣:

• Multicolumn index on 3 fields with heterogenous data types
• Improving performance with a Similarity Postgres fuzzy self join query