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

Invalid syntax for integer

From: David Jarvis <thangalin(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Invalid syntax for integer
Date: 2010-07-01 22:24:47
Message-ID: AANLkTimeb6Yekp-OQXiLR5BlS_HfeWR88RyFdo7MK1ya@mail.gmail.com (view raw or flat)
Thread:
Lists: 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):

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*}

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}

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;

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-07-01 23:11:34
Subject: Re: Invalid syntax for integer
Previous:From: Atif JungDate: 2010-07-01 16:30:28
Subject: Re: AUTO COMMIT

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