Re: invalid input syntax in recursive function

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