参数错误的postgresql调用函数

Question

我声明了 postgresql 函数如下，这个函数简单地计算以公里为单位的两点之间的距离

CREATE OR REPLACE FUNCTION show_places(ref refcursor,lat float8,lon float8, radius double precision) RETURNS refcursor AS $$
BEGIN
  OPEN ref FOR SELECT * FROM place p where ST_Distance(ST_Point(lon,lat)::geography,ST_Point(p.lon,p.lat)::geography) <= radius;    -- Open a cursor
  RETURN ref;                                                        
END;
$$ LANGUAGE plpgsql;

调用如下:

BEGIN;
SELECT show_places('cities_cur',44.379,-79.703,255621.82229418);
FETCH ALL IN "cities_cur";
COMMIT;

但是我遇到了这个 SQL 异常:

Answer 1

对于变量和表列的相同名称越来越混淆。只需更改变量的名称

CREATE OR REPLACE FUNCTION show_places(ref refcursor,v_lat float8,v_lon float8, radius double precision) RETURNS refcursor AS $$
BEGIN
  OPEN ref FOR SELECT * FROM place p where ST_Distance(ST_Point(v_lon,v_lat)::geography,ST_Point(p.lon,p.lat)::geography) <= radius;    -- Open a cursor
  RETURN ref;                                                        
END;
$$ LANGUAGE plpgsql;