invalid input syntax in recursive function

From: Mohammed Kashim <M(dot)G(dot)A(dot)Kashim(at)student(dot)bradford(dot)ac(dot)uk>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: invalid input syntax in recursive function
Date: 2016-02-25 10:10:01
Message-ID: AM3PR03MB1057450B2832535AC700D35CFBA60@AM3PR03MB1057.eurprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message s d 2016-02-25 13:08:10 Re: invalid input syntax in recursive function
Previous Message Killian Driscoll 2016-02-21 21:11:55 Re: Create view that retrieves both table and column comments