1

I am trying to execute a string SQL query in function i.e.

Create or Replace Function Route(edge_table text)
Returns table(sequence integer) as $$
Begin
Return Query Execute 'with r as(
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra( ' || '''SELECT id, source, target, cost FROM ' || edge_table || ' ''' || ', 7, 12, false, false );
)
select seq from r';
End;
$$ language plpgsql;

When I run function gets created, but when I call this function it says verify your query is not correct.

Actually dijkstra takes an string sql.like that,

SELECT seq, id1 AS node, id2 AS edge, cost 
FROM pgr_dijkstra( **'SELECT id, source, target, cost FROM edge_table'**, 7, 12, false, false );

I want table name to be dynamic, how can I achieve this?

  • Please post the exact and complete error message – JGH Sep 9 '18 at 21:15
  • Generally, if you want to use a dynamic table name, it is better to use execute format. See, for example, this answer on stack exchange. – John Powell Sep 10 '18 at 9:10
0

There is a semicolon that shouldn't be there in the textual expression to be executed (... 7, 12, false, false );), causing the reminder of the expression to be ignored (select seq from r).

Try removing it

Returns table(sequence integer) as $$
Begin
Return Query Execute 'with r as(
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra( ' || '''SELECT id, source, target, cost FROM ' || edge_table || ' ''' || ', 7, 12, false, false )
)
select seq from r';
End;
$$ language plpgsql;
  • Also, use the concat operator, it is much easier to follow than all those ||, imho. – John Powell Sep 10 '18 at 9:16

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.