postgres - ERROR: operator does not exist
- by cino21122
Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself...
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.
The query is this:
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);
The PG function is this:
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;
