drop function if exists PgRoutingRoute( geometry, geometry );

create or replace function PgRoutingRoute(point_from geometry, point_to geometry)
    returns geometry as $$
    declare
        result geometry;
begin
    create temporary table start_edge as (
        select *
        from routing.ways
        order by the_geom <-> point_from
        limit 1
    );
    create temporary table end_edge as (
        select *
        from routing.ways
        order by the_geom <-> point_to
        limit 1
    );
    create temporary table pgrouting_result as (
        select *
        from pgr_dijkstra(
            'SELECT gid AS id, source, target, ST_Length(the_geom::geography)/greatest(fwd_speed_mps, 0.000001) as cost, -ST_Length(the_geom::geography)/least(bwd_speed_mps, -0.000001) as reverse_cost FROM speeded_ways where the_geom && (select ST_Expand(ST_Extent(ST_MakeLine('''
            || point_from :: text || ''',''' || point_to :: text || ''')), 0.01))' :: text,
            (
                select target
                from start_edge
            ),
            (
                select source
                from end_edge
            )
        )
    );

    --TODO determine how to connect first and last segments. And do we really need them?
    /*
    if (select target from start_edge) = (select node from pgrouting_result order by seq limit 1) then
        start_weight = (select st_length(the_geom::geography)*(1.0 - ST_LineLocatePoint(the_geom, point_from))*3.6/maxspeed_forward from start_edge);
    else
        start_weight = (select st_length(the_geom::geography)*(ST_LineLocatePoint(the_geom, point_from))*3.6/maxspeed_backward from start_edge);
    end if;

    end_weight = (select st_length(the_geom::geography)*(1.0 - ST_LineLocatePoint(the_geom, point_to))*3.6/maxspeed_forward from end_edge);
*/
    drop table if exists start_edge;
    drop table if exists end_edge;

    result = (
        select ST_MakeLine(
            ST_AddMeasure(
                case when node=ways.source then
                    ways.the_geom
                else
                    ST_Reverse(ways.the_geom)
                end,
                (/*start_weight + end_weight +*/ maxcost - agg_cost),
                (/*start_weight + end_weight +*/ maxcost - agg_cost - pgrouting_result.cost)
            )
        )
        from (
                 select sum(cost) as maxcost
                 from pgrouting_result
             ) as maxcost,
            pgrouting_result
            left join routing.ways on pgrouting_result.edge = routing.ways.gid
    );

    drop table if exists pgrouting_result;

    return result;

end
$$ language plpgsql strict;