Re: invalid input syntax in recursive function - Mailing list pgsql-novice
| From | s d |
|---|---|
| Subject | Re: invalid input syntax in recursive function |
| Date | |
| Msg-id | CAKyoTgYLzprs_P49pQwOR+9eVjr3eK3pBgK=w=7ezWn2Sytdjg@mail.gmail.com Whole thread Raw |
| In response to | invalid input syntax in recursive function (Mohammed Kashim <M.G.A.Kashim@student.bradford.ac.uk>) |
| List | 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
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
pgsql-novice by date: