sql - 如何在sql中为函数选择没有行ID和列名的结果值？

Question

我执行 pgRouting，我需要从我的 SELECT 中将整数结果插入 dijkstra 算法。

SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
  FROM pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
34, 3000, false
  ) as di
  JOIN planet_osm_roads pt
  ON di.edge = pt.osm_id ;

这是有效的，但是如果我想用这个查询将 dijkstra 函数中的节点 34 替换为我的街道的节点号:

SELECT pl.source::integer 
FROM planet_osm_roads pl 
WHERE pl.name LIKE ''street_name'' 
LIMIT 1

一起:

SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
  FROM pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
'SELECT pl.source::integer FROM planet_osm_roads pl WHERE pl.name LIKE ''street_name'' LIMIT 1',
 3000, false
  ) as di
  JOIN planet_osm_roads pt
  ON di.edge = pt.osm_id ;

它会因错误而失败:

ERROR:  function pgr_dijkstra(unknown, unknown, integer, boolean) is not unique
LINE 93:   FROM pgr_dijkstra(
                ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

我认为，这是因为我的选择查询返回带有行 ID 和列名的 sql 结果。但也许还有另一个问题。

如何只输出一个整数？

Answer 1

不要将查询作为字符串传递:

SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM pgr_dijkstra(
  'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
  (SELECT pl.source::integer FROM planet_osm_roads pl WHERE pl.name LIKE 'street_name' LIMIT 1),
   3000, false
) as di
  JOIN planet_osm_roads pt
  ON di.edge = pt.osm_id;

或者使用派生表

SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM (
  SELECT pl.source::integer as source
  FROM planet_osm_roads pl 
  WHERE pl.name 
  LIKE 'street_name' LIMIT 1
) pl 
  join lateral pgr_dijkstra(
    'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
    pl.source,
    3000, false
  ) as di on true
  JOIN planet_osm_roads pt ON di.edge = pt.osm_id;