Skip site navigation (1) Skip section navigation (2)

Re: Invalid syntax for integer

From: David Jarvis <thangalin(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Invalid syntax for integer
Date: 2010-07-02 00:50:48
Message-ID: AANLkTin9Gv1I1S5U3-UM0vRQNUiiypuP7WV_Dg4gcZYx@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: David JarvisDate: 2010-07-02 00:53:05
Subject: Re: Invalid syntax for integer
Previous:From: Tom LaneDate: 2010-07-01 23:11:34
Subject: Re: Invalid syntax for integer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group