Hi,
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;
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"
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