1

I'm trying to create a function that will be essentially taking various parameters perform pgrouting with Dijkstra's algorithm, according to the user input as parameters. So the I'm making a minimal example which is the following:

 CREATE OR REPLACE FUNCTION _a_route(source integer, target integer)
 RETURNS SETOF record AS
 $$
 BEGIN
 SELECT * FROM pgr_dijkstra('SELECT id, source, target, time AS cost FROM roads_noded', $1, $2, false);
 END;
 $$
 LANGUAGE plpgsql;

This should be taking source and target as input and run the algorithm.

But I'm always getting the same error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM       instead.
CONTEXT:  PL/pgSQL function _a_route(integer,integer) line 3 at SQL statement

So what am I doing wrong here? SETOF record is supposed to be the equivalent of returning a table

Postgresql Version: 9.6.1 PostGIS : 2.3.1

2

Just use SQL and also it's better not to return record, since you'd then always have to specify the columns when you use the function.

Do

CREATE OR REPLACE FUNCTION _a_route(source integer, target integer)
RETURNS TABLE(seq integer, path_seq integer, node bigint, edge bigint, cost double precision, agg_cost double precision) AS
 $$
  SELECT d.seq, d.path_seq, d.node, d.edge, d.cost, d.agg_cost 
    FROM pgr_dijkstra(
  'SELECT id, source, target, time AS cost FROM roads_noded', $1, $2,    
   false) AS d;
$$
LANGUAGE sql;

If you do use plpgsql, you have to do RETURN QUERY right before your SELECT statement, but in this case you don't need to use plpgsql.

This and many other pgrouting topics are covered in our upcoming book which is now in preview release and next preview release will be coming out early next week which includes the remaining chapters in draft form. http://locatepress.com/pgrouting

  • Thanks a lot....!!! You are right. There's no need for it to be plpgsql. So now that I have the function created, I assume that I need to be able to pass the parameters in mapserver's mapfile ? How's that possible ? I've been waiting for this book for some time now. When will it be fully released? – Stefanos Jan 6 '17 at 0:43
  • Probably another month or 2. We just finished up the content now it's polishing time. But it has started selling and anyone that buys gets polished updates. Regarding mapserver - you'd use runtime substitution - mapserver.org/cgi/runsub.html and then your DATA part you'd have something like DATA "geom FROM (SELECT r.*, e.geom FROM _a_route(%source%, %target%) AS r INNER JOIN your_edges AS e ON r.eget = e.id" and pass the variables via http get – LR1234567 Jan 6 '17 at 3:00
  • Thank you very much...!! One more thing: the function I'm creating is not quite returning a table. It more like a comma separated value list under one column name "_a_router" and then "record". Is this a proper result? I assume that the result will be the same if the function returns either a "SETOF record" or "table", right ? And apart from Runtime Subsitution, do you think php Mapscript would be another solution for passing values to the mapfile this way? – Stefanos Jan 6 '17 at 12:55
  • 1
    If you call it with SELECT f.* FROM _a_route(1,7) As f. It should return all the rows and columns. Don't call it like SELECT _a_route(1,7); which is what I suspect you are doing – LR1234567 Jan 6 '17 at 14:27
  • Hello again...!! I've been trying this in a minimal example, meaning without using the runtime substitution yet but no chance so far. The statement is: DATA: "geom FROM (SELECT r.*, e.geom FROM _a_route(1,4)) AS r INNER JOIN roads_noded AS e ON r.edge = e.id using unique id using srid=2100". The MS_ERRORFILE gives me the following error: Error (ERROR: subquery in FROM must have an alias LINE 1: ...T_Force2D("geom"),'NDR'),'hex') as geom,"id" from (SELECT r.... I hope this is not a type. The statement looks OK so far... – Stefanos Jan 10 '17 at 16:50
1

There are two ways to do that in plpgsql.

Return table:

CREATE OR REPLACE FUNCTION _a_route(source integer, target integer)
 RETURNS TABLE(id integer, source integer, target integer, cost double precision) AS
 $$
 BEGIN
   RETURN QUERY SELECT id, source, target, cost FROM pgr_dijkstra('SELECT id, source, target, time AS cost FROM roads_noded', $1, $2, false);
 END;
 $$
 LANGUAGE plpgsql;

Return SETOF Record with OUT params:

CREATE OR REPLACE FUNCTION _a_route(source integer, target integer, id OUT integer, source OUT integer, target OUT integer, cost OUT double precision)
 RETURNS SETOF TABLE(id integer, source integer, target integer, cost double precision) AS
 $$
 BEGIN
   RETURN QUERY SELECT id, source, target, cost FROM pgr_dijkstra('SELECT id, source, target, time AS cost FROM roads_noded', $1, $2, false);
 END;
 $$
 LANGUAGE plpgsql;

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.