1

I have first a selection of trip_id (for a train or a bus e.g.) in a (sub-)Table A

trip_id

350
351
360

and want to use a self-written function which takes the used trip_id and gives back the a TABLE with trip_id, used roads (in form of a road_id) and the sequence seq of stops for this trip (here for example trip no. 350):

SELECT * FROM getroutepertrip(350)
-----
trip_id   road_id    seq

350       a1        1
350       g4        2
350       a2        3
 .        .         .
 .        .         .
 .        .         .

The desired oucome of the whole problem should be like this above but for all the trips from the CTE subselect. Unfortunately, my function only can take a single trip_id. My first and only attempt is of course wrong since a subquery can't contain multiple columns

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)

SELECT trip_id,  (SELECT * FROM getroutepertrip(trip_id))
FROM A 
GROUP BY trip_id
ORDER by trip_id

Hopefully somebody can help me here, it feels like I'm mentally in a deadlock...

1

You want to do a LATERAL join, by putting the the function in the FROM list. If you just put the function in the from list after table "a", you don't need the LATERAL keyword, as joins to functions are automatically lateral. If you wrap it in a dummy "select * from", then you do need the keyword:

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A CROSS JOIN LATERAL (SELECT * FROM getroutepertrip(trip_id)) as route 
GROUP BY trip_id
ORDER by trip_id

or

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A, getroutepertrip(trip_id) as route 
GROUP BY trip_id
ORDER by trip_id
  • Yes, that was my second example, without the subquery. I thought I should show how to do it with the subquery, in case he needs to refine it somehow. True enough, he needs to either get rid of the group by, or add aggregates to the non-grouped by columns, or just remove them from the select list, but I don't which of those he wants. – jjanes Oct 10 at 16:43
  • Thanks already! Do you have any idea how to avoid the group by e.g.? – sequoia Oct 14 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.