Thread: invalid input syntax in recursive function

invalid input syntax in recursive function

From

Mohammed Kashim

Date:

25 February 2016, 10:11:18

Hello,

Apologies if this is the wrong list to contact. I am new to PostgreSQL and I am currently in the process of writing a recursive function to find tram times.

CREATE TYPE single_journey AS

(tram_id integer,

departure_station text,

departure_time time without time zone,

destination_station text,

arrival_time time without time zone);

CREATE OR REPLACE FUNCTION find_tram_same_line(text, text, time) returns single_journey AS $$

DECLARE

departure_station ALIAS FOR $1;

destination_station ALIAS FOR $2;

query_time ALIAS FOR $3;

journey single_journey;

BEGIN

journey.departure_station := departure_station;

journey.destination_station := destination_station;

SELECT tram_id, time

INTO journey.tram_id, journey.departure_time

FROM station_departure_times

JOIN stations on station_departure_times.station_id = stations.station_id

WHERE stations.name = departure_station

AND time > query_time

ORDER BY time ASC

LIMIT 1;

SELECT time

INTO journey.arrival_time

FROM station_departure_times

JOIN stations on station_departure_times.station_id = stations.station_id

WHERE stations.name = destination_station

AND tram_id = journey.tram_id;

IF journey.arrival_time IS NULL THEN

SELECT find_tram_same_line(

departure_station,

destination_station,

(query_time + interval '1 minute'))

INTO journey;

END IF;

RETURN journey;

END;

$$ LANGUAGE plpgsql;

SELECT find_tram_same_line('GrimesDyke', 'CitySquare', '09:00:00');

Whenever I run the query (highlighted in bold), I get an error:

********** Error **********

ERROR: invalid input syntax for integer: "(24,GrimesDyke,09:07:00,CitySquare,10:19:00)"

SQL state: 22P02

Context: PL/pgSQL function find_tram_same_line(text,text,time without time zone) line 29 at SQL statement

I have spent some time trying to figure out why this is to no avail. The only integer in the single_journey type is the tram_id but I am unsure why this is causing an issue. Does anyone know why this might be?

Thanks,

Re: invalid input syntax in recursive function

From

s d

Date:

25 February 2016, 13:08:23

On 25 February 2016 at 11:10, Mohammed Kashim <M.G.A.Kashim@student.bradford.ac.uk> wrote:

Hello,

Apologies if this is the wrong list to contact. I am new to PostgreSQL and I am currently in the process of writing a recursive function to find tram times.

CREATE TYPE single_journey AS
   (tram_id integer,
    departure_station text,
    departure_time time without time zone,
    destination_station text,
    arrival_time time without time zone);

CREATE OR REPLACE FUNCTION find_tram_same_line(text, text, time) returns single_journey AS $$
DECLARE
     departure_station ALIAS FOR $1;
     destination_station ALIAS FOR $2;
     query_time ALIAS FOR $3;

     journey single_journey;
BEGIN
     journey.departure_station := departure_station;
     journey.destination_station := destination_station;

     SELECT tram_id, time
     INTO journey.tram_id, journey.departure_time
     FROM station_departure_times
     JOIN stations on station_departure_times.station_id = stations.station_id
     WHERE stations.name = departure_station
     AND time > query_time
     ORDER BY time ASC
     LIMIT 1;

     SELECT time
     INTO journey.arrival_time
     FROM station_departure_times
     JOIN stations on station_departure_times.station_id = stations.station_id
     WHERE stations.name = destination_station
     AND tram_id = journey.tram_id;

     IF journey.arrival_time IS NULL THEN
           SELECT find_tram_same_line(
                departure_station,
                destination_station,
                (query_time + interval '1 minute'))
           INTO journey;
     END IF;

     RETURN journey;

END;
$$ LANGUAGE plpgsql;

SELECT find_tram_same_line('GrimesDyke', 'CitySquare', '09:00:00');

Whenever I run the query (highlighted in bold), I get an error:

********** Error **********

ERROR: invalid input syntax for integer: "(24,GrimesDyke,09:07:00,CitySquare,10:19:00)"
SQL state: 22P02
Context: PL/pgSQL function find_tram_same_line(text,text,time without time zone) line 29 at SQL statement

I have spent some time trying to figure out why this is to no avail. The only integer in the single_journey type is the tram_id but I am unsure why this is causing an issue. Does anyone know why this might be?

Thanks,
Mo

Your problem is this part:

SELECT find_tram_same_line(

departure_station,

destination_station,

(query_time + interval '1 minute'))

INTO journey;

Set returning functions don't work that way.

If your function actually worked it would give you something like you see in the error message:

(24,GrimesDyke,09:07:00,CitySquare,10:19:00)

It returns a whole record(in fact a whole table but only one record in it) as the result.

When you call it inside the function it returns only one value(the whole composite type).

The INTO statement tries to put all values into your target structure one by one, so the first value(the whole journey composite) goes into the first field(tram_id).

First you should call it in this way: SELECT * from find_tram_same_line('GrimesDyke', 'CitySquare', '09:00:00'); to get something useful from it.

And that's true inside your function too:

SELECT * from find_tram_same_line(

departure_station,

destination_station,

(query_time + interval '1 minute'))

INTO journey;

Regards,

Sándor