[mapserver-users] PostGIS / pgRouting error in mapfile

[Stefanos Anastasiou]

Hi list. Happy new year. :-)

I want to perform pgRouting with MapServer so I've create a function in postgis which I'll then be passing parameters with Runtime Substitution (or mapscript is a better idea?) which is the following:

CREATE OR REPLACE FUNCTION routing(source integer, target integer)
RETURNS TABLE(seq integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision, street text, geom text) AS
$$
   SELECT a.*, b.street, ST_AsText(b.geom) FROM 
   pgr_dijkstra('SELECT id, source, target, time AS cost FROM roads_noded', $1,$2, false) AS a
   INNER JOIN roads_noded AS b ON a.edge = b.id;
$$
LANGUAGE 'sql';

So, if I run this function by itself it returns all the results of dijkstra's algorithm PLUS the name of the street and the geometry in ST_AsText from of my road network.

I'm trying to put the query in the mapfile like this (minimal example): 

   CONNECTIONTYPE POSTGIS
   CONNECTION 'host=localhost port=5432 user=user password=somePass dbname=myDb'
   DATA "geom FROM (SELECT r.* FROM _a_router(1,24)) AS r INNER JOIN roads_noded as e ON e.id = r.edge using unique e.id using srid=2100"

but it won't work. The MS_ERRORFILE gives the following error:
  
   
msPostGISLayerWhichShapes(): Error (ERROR:  missing FROM-clause entry for table "r"
LINE 1: ..."geom"),'NDR'),'hex') as geom,"e.id" from (SELECT r.* FROM _...
                                                             ^

I'm not following here... I have the from clause. How should I write the query?

Regards,
Stefanos

PS.: I hope this isn't off topic ...

Specs: 
MapServer version 7.0.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=XMP SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE


POSTGIS=2.3.1