我声明了postgresql函数,如下所示,该函数仅计算kmeter中2个点之间的距离
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异常:
它对变量和表列的相同名称感到困惑。只需更改变量名称
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句