Invalid syntax for integer
| От | David Jarvis |
|---|---|
| Тема | Invalid syntax for integer |
| Дата | |
| Msg-id | AANLkTimeb6Yekp-OQXiLR5BlS_HfeWR88RyFdo7MK1ya@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Invalid syntax for integer
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-novice |
Hi,
I have two overloaded functions:
CREATE FUNCTION climate.plr_stations(csv_latitude text, csv_longitude text)
CREATE FUNCTION climate.plr_stations(v_id integer[], v_latitude double precision[], v_longitude double precision[], latitude double precision[], longitude double precision[])
The first function takes a comma-separated list of values and converts them to arrays before passing them to the second. The variables are declared in the first function as follows:
latitude double precision[];
longitude double precision[];
v_id integer[];
v_latitude double precision[];
v_longitude double precision[];
latitude := string_to_array( csv_latitude, ',' );
longitude := string_to_array( csv_longitude, ',' );
The second function is called from the first as follows:
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
The error message is:
ERROR: invalid input syntax for integer: "-123.1833"
The variables have the following values (see the source code for where box is assigned):
The second function is written in PL/R. I have included the full source of the first function below.
The only parameter that takes an integer value is the first: v_id. Everything else is a double precision (or array of double precision).
The same code runs successfully with more values. For example:
Using a single value for v_id, v_latitude and v_longitude, however, does not.
I have tried adding typecasts (::double precision[]) to v_latitude and v_longitude without success.
Any ideas?
Thank you.
Dave
--
I have two overloaded functions:
CREATE FUNCTION climate.plr_stations(csv_latitude text, csv_longitude text)
CREATE FUNCTION climate.plr_stations(v_id integer[], v_latitude double precision[], v_longitude double precision[], latitude double precision[], longitude double precision[])
The first function takes a comma-separated list of values and converts them to arrays before passing them to the second. The variables are declared in the first function as follows:
latitude double precision[];
longitude double precision[];
v_id integer[];
v_latitude double precision[];
v_longitude double precision[];
latitude := string_to_array( csv_latitude, ',' );
longitude := string_to_array( csv_longitude, ',' );
The second function is called from the first as follows:
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
The error message is:
ERROR: invalid input syntax for integer: "-123.1833"
The variables have the following values (see the source code for where box is assigned):
NOTICE: box: (49.17583,-123.21528,49.21083,-123.14387)
NOTICE: latitude: {49.21083,49.20903,49.17583,49.1875,49.21083}
NOTICE: longitude: {-123.21528,-123.14387,-123.15349,-123.21254,-123.21528}
NOTICE: v_id: {967}
NOTICE: v_latitude: {49.2}
NOTICE: v_longitude: {-123.1833}
NOTICE: latitude: {49.21083,49.20903,49.17583,49.1875,49.21083}
NOTICE: longitude: {-123.21528,-123.14387,-123.15349,-123.21254,-123.21528}
NOTICE: v_id: {967}
NOTICE: v_latitude: {49.2}
NOTICE: v_longitude: {-123.1833}
The second function is written in PL/R. I have included the full source of the first function below.
The only parameter that takes an integer value is the first: v_id. Everything else is a double precision (or array of double precision).
The same code runs successfully with more values. For example:
2010-07-01 15:18:53 PDT NOTICE: box: (49.195,-123.28056,49.29583,-123.00528)
2010-07-01 15:18:53 PDT NOTICE: latitude: {49.29583,49.21167,49.195,49.27806,49.29583}
2010-07-01 15:18:53 PDT NOTICE: longitude: {-123.02861,-123.00528,-123.20889,-123.28056,-123.02861}
2010-07-01 15:18:53 PDT NOTICE: v_id: {765,924,952,953,954,958,959,961,962,963,964,965,967,968,969,970,971,972,973,974,976,977,978,980,981,982,983,984,985,986,920,987,988,989,990}
2010-07-01 15:18:53 PDT NOTICE: v_latitude: {49.2167,49.2,49.25,49.2333,49.2833,49.2333,49.25,49.2833,49.2667,49.2,49.2667,49.2833,49.2,49.2667,49.2333,49.2333,49.25,49.2667,49.2667,49.2,49.2667,49.2167,49.2333,49.2833,49.2333,49.2333,49.2167,49.2333,49.2667,49.25,49.2,49.2167,49.2667,49.2667,49.2667}
2010-07-01 15:18:53 PDT NOTICE: v_longitude: {-123.0167,-123.0333,-123.05,-123.0167,-123.1167,-123.1833,-123.1833,-123.05,-123.1333,-123.05,-123.0333,-123.05,-123.1833,-123.1667,-123.1333,-123.1667,-123.0833,-123.1667,-123.1667,-123.1333,-123.1,-123.1333,-123.1167,-123.1167,-123.15,-123.0333,-123.0667,-123.1833,-123.15,-123.25,-123.1,-123.05,-123.1667,-123.2167,-123.2}
2010-07-01 15:18:53 PDT NOTICE: latitude: {49.29583,49.21167,49.195,49.27806,49.29583}
2010-07-01 15:18:53 PDT NOTICE: longitude: {-123.02861,-123.00528,-123.20889,-123.28056,-123.02861}
2010-07-01 15:18:53 PDT NOTICE: v_id: {765,924,952,953,954,958,959,961,962,963,964,965,967,968,969,970,971,972,973,974,976,977,978,980,981,982,983,984,985,986,920,987,988,989,990}
2010-07-01 15:18:53 PDT NOTICE: v_latitude: {49.2167,49.2,49.25,49.2333,49.2833,49.2333,49.25,49.2833,49.2667,49.2,49.2667,49.2833,49.2,49.2667,49.2333,49.2333,49.25,49.2667,49.2667,49.2,49.2667,49.2167,49.2333,49.2833,49.2333,49.2333,49.2167,49.2333,49.2667,49.25,49.2,49.2167,49.2667,49.2667,49.2667}
2010-07-01 15:18:53 PDT NOTICE: v_longitude: {-123.0167,-123.0333,-123.05,-123.0167,-123.1167,-123.1833,-123.1833,-123.05,-123.1333,-123.05,-123.0333,-123.05,-123.1833,-123.1667,-123.1333,-123.1667,-123.0833,-123.1667,-123.1667,-123.1333,-123.1,-123.1333,-123.1167,-123.1167,-123.15,-123.0333,-123.0667,-123.1833,-123.15,-123.25,-123.1,-123.05,-123.1667,-123.2167,-123.2}
Using a single value for v_id, v_latitude and v_longitude, however, does not.
I have tried adding typecasts (::double precision[]) to v_latitude and v_longitude without success.
Any ideas?
Thank you.
Dave
--
CREATE OR REPLACE FUNCTION climate.plr_stations(csv_latitude text, csv_longitude text)
RETURNS SETOF integer AS
$BODY$ DECLARE
latitude double precision[];
longitude double precision[];
s_id integer;
s_latitude double precision;
s_longitude double precision;
v_id integer[];
v_latitude double precision[];
v_longitude double precision[];
box climate.box;
result integer[];
BEGIN
latitude := string_to_array( csv_latitude, ',' );
longitude := string_to_array( csv_longitude, ',' );
IF (array_length( latitude, 1 ) > 4) AND (array_length( longitude, 1 ) > 4) THEN
SELECT * FROM climate.plr_polygon_bounds( latitude, longitude ) INTO box;
RAISE NOTICE 'box: %', box;
-- Get all stations within the polygon's bounding box.
SELECT
array_agg( s.id ),
array_agg( s.latitude_decimal ),
array_agg( s.longitude_decimal )
INTO
v_id,
v_latitude,
v_longitude
FROM
climate.station s
WHERE
s.applicable AND
s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max;
RAISE NOTICE 'latitude: %', latitude;
RAISE NOTICE 'longitude: %', longitude;
RAISE NOTICE 'v_id: %', v_id;
RAISE NOTICE 'v_latitude: %', v_latitude;
RAISE NOTICE 'v_longitude: %', v_longitude;
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
END IF;
RETURN QUERY SELECT * FROM unnest( result );
END;
RETURNS SETOF integer AS
$BODY$ DECLARE
latitude double precision[];
longitude double precision[];
s_id integer;
s_latitude double precision;
s_longitude double precision;
v_id integer[];
v_latitude double precision[];
v_longitude double precision[];
box climate.box;
result integer[];
BEGIN
latitude := string_to_array( csv_latitude, ',' );
longitude := string_to_array( csv_longitude, ',' );
IF (array_length( latitude, 1 ) > 4) AND (array_length( longitude, 1 ) > 4) THEN
SELECT * FROM climate.plr_polygon_bounds( latitude, longitude ) INTO box;
RAISE NOTICE 'box: %', box;
-- Get all stations within the polygon's bounding box.
SELECT
array_agg( s.id ),
array_agg( s.latitude_decimal ),
array_agg( s.longitude_decimal )
INTO
v_id,
v_latitude,
v_longitude
FROM
climate.station s
WHERE
s.applicable AND
s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max;
RAISE NOTICE 'latitude: %', latitude;
RAISE NOTICE 'longitude: %', longitude;
RAISE NOTICE 'v_id: %', v_id;
RAISE NOTICE 'v_latitude: %', v_latitude;
RAISE NOTICE 'v_longitude: %', v_longitude;
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
END IF;
RETURN QUERY SELECT * FROM unnest( result );
END;
В списке pgsql-novice по дате отправления: