Re: invalid input syntax in recursive function

On 25 February 2016 at 11:10, Mohammed Kashim <
M(dot)G(dot)A(dot)Kashim(at)student(dot)bradford(dot)ac(dot)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