postgresql error - ERROR: input is out of range
- by CaffeineIV
The function below keeps returning this error message. I thought that maybe the double_precision field type was what was causing this, and I tried to use CAST, but either that's not it, or I didn't do it right... Help?
Here's the error:
ERROR: input is out of range
CONTEXT: PL/pgSQL function "calculate_distance" line 7 at RETURN
********** Error **********
ERROR: input is out of range
SQL state: 22003
Context: PL/pgSQL function "calculate_distance" line 7 at RETURN
And here's the function:
CREATE OR REPLACE FUNCTION calculate_distance(character varying,
double precision, double precision,
double precision, double precision)
RETURNS double precision AS
$BODY$
DECLARE earth_radius double precision;
BEGIN
earth_radius := 3959.0;
RETURN earth_radius * acos(sin($2 / 57.2958) *
sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958)
* cos(($5 / 57.2958) - ($3 / 57.2958)));
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION calculate_distance(character varying,
double precision, double precision, double precision,
double precision) OWNER TO postgres;
//I tried changing (unsuccessfully) that RETURN line to:
RETURN CAST( (earth_radius * acos(sin($2 / 57.2958)
* sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958)
* cos(($5 / 57.2958) - ($3 / 57.2958))) ) AS text);
