Обсуждение: Invalid syntax for integer
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;
David Jarvis <thangalin@gmail.com> writes:
> The error message is:
> ERROR: invalid input syntax for integer: "-123.1833"
Presumably, this is coming from where you try to assign the result of
array_agg to v_id. Have you looked at what's going to be assigned
there?
regards, tom lane
Hi,
I added the following code:
For the error condition, the data looks as follows:
If I comment out the following line, then there are no errors:
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
The following line, without aggregated variables, receives an error:
result := climate.plr_stations( ARRAY[967], ARRAY[49.2], ARRAY[-123.1833], latitude, longitude );
This leaves me to believe that there is nothing wrong with the array aggregation.
The following line does not generate errors:
Now I have a work-around: I can explicitly set the second (i.e., "last") value of the array to NULL when the array has only one element.
I do not understand why having more than two elements works, but a single value does not.
Any ideas?
Dave
I added the following code:
FOR r IN
SELECT
*
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
LOOP
RAISE NOTICE 'r.id: %', r.id;
RAISE NOTICE 'r.lat_dec: %', r.latitude_decimal;
RAISE NOTICE 'r.lng_dec: %', r.longitude_decimal;
END LOOP;
SELECT
*
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
LOOP
RAISE NOTICE 'r.id: %', r.id;
RAISE NOTICE 'r.lat_dec: %', r.latitude_decimal;
RAISE NOTICE 'r.lng_dec: %', r.longitude_decimal;
END LOOP;
For the error condition, the data looks as follows:
NOTICE: box: (49.17583,-123.21528,49.21083,-123.14387)
NOTICE: r.id: 967
NOTICE: r.lat_dec: 49.2000
NOTICE: r.lng_dec: -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}
ERROR: invalid input syntax for integer: "-123.1833"
NOTICE: r.id: 967
NOTICE: r.lat_dec: 49.2000
NOTICE: r.lng_dec: -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}
ERROR: invalid input syntax for integer: "-123.1833"
If I comment out the following line, then there are no errors:
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
The following line, without aggregated variables, receives an error:
result := climate.plr_stations( ARRAY[967], ARRAY[49.2], ARRAY[-123.1833], latitude, longitude );
This leaves me to believe that there is nothing wrong with the array aggregation.
The following line does not generate errors:
result := climate.plr_stations( ARRAY[967,NULL], ARRAY[49.2], ARRAY[-123.1833], latitude, longitude );
Now I have a work-around: I can explicitly set the second (i.e., "last") value of the array to NULL when the array has only one element.
I do not understand why having more than two elements works, but a single value does not.
Any ideas?
Dave
$ psql --version
psql (PostgreSQL) 8.4.4
psql (PostgreSQL) 8.4.4
Hi,
The work-around is as follows:
IF array_length( v_id, 1 ) = 1 THEN
SELECT array_append( v_id, NULL ) INTO v_id;
END IF;
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
This now works when v_id contains a single element.
Dave
The work-around is as follows:
IF array_length( v_id, 1 ) = 1 THEN
SELECT array_append( v_id, NULL ) INTO v_id;
END IF;
result := climate.plr_stations( v_id, v_latitude, v_longitude, latitude, longitude );
This now works when v_id contains a single element.
Dave