我有一个在本地运行良好的函数,但是将其移动到线上却出现了一个大错误... 从一个回答中得到启示,其中有人指出我传递的参数数量不准确,我在这种情况下再次仔细检查,确保我向函数本身传递了5个参数...
Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
这是查询的内容:
BEGIN; SELECT zip_proximity_sum('zc',
(SELECT g.lat FROM geocoded g
LEFT JOIN masterfile m ON g.recordid = m.id
WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),
(SELECT g.lon FROM geocoded g
LEFT JOIN masterfile m ON g.recordid = m.id
WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),
(SELECT m.zip FROM geocoded g
LEFT JOIN masterfile m ON g.recordid = m.id
WHERE m.zip = '10050' ORDER BY m.id LIMIT 1)
,10);
PG函数是这样的:
CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric)
RETURNS refcursor AS
$BODY$
BEGIN
OPEN $1 FOR
SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
FROM
geocoded g LEFT JOIN masterfile r ON g.recordid = r.id
WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5)
ORDER BY r.zip, distance;
RETURN $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
cube
和earthdistance
扩展,重启了PostgreSQL。但是出现了[42883] ERROR: operator does not exist: point <@> point
错误。这可能与Windows上的PostgreSQL v11有关吗? - thames