1

I am trying to insert a series of values as an array into a function where an array can be defined. The array values come from another SQL function that gives a table of the values I want to use in the proceeding function, which is the pgr_dijkstra ONE to MANY option which requires an array.

How the function works normally: 

SELECT * 
FROM pgr_dijkstra('SELECT id,source,target,cost FROM my_table', start_id, ARRAY(1,2,3,4));

How I roughly thought the function should work if I were to try and insert this array into it, but does not and 

SELECT foo.* 
FROM (SELECT ARRAY(SELECT DISTINCT id::integer 
                   FROM (bla bla) AS ARRAY_QUERY, 
     pgr_dijkstra('SELECT id, source,target,cost FROM my_table', start_id, ARRAY_QUERY) AS foo;

As this only gives:

ERROR:  function pgr_dijkstra(unknown, integer, record, boolean) does not exist

And the array is created as an integer and not a record, so not sure why this is occuring.

Possibly because I do not fully understand how arrays work in postgresql even after reading documentation on the subject and how to implement values from one query into another where they are required as array input. Hopefully there is a workaround to this or just simply some step I have missed in implementing this properly.

Solved: https://stackoverflow.com/a/42859060/7056396

Community
  • 1
  • 1
leffe86
  • 13
  • 4
  • Possible duplicate of [Concatenate multiple rows in an array with SQL on PostgreSQL](http://stackoverflow.com/questions/533256/concatenate-multiple-rows-in-an-array-with-sql-on-postgresql) – Juan Carlos Oropeza Mar 17 '17 at 13:34
  • Also instead of using a subquery is better if you create a cte, Im almost sure that sintaxis isnt the one you need. – Juan Carlos Oropeza Mar 17 '17 at 13:39
  • I am unfamiliar with 'cte' and 'sintaxis' terminology. But the previous solution mentioned by http://stackoverflow.com/a/42859060/7056396 works as I had intended it too. – leffe86 Mar 17 '17 at 13:57

1 Answers1

1
select * 
from pgr_dijkstra(
    'select id,source,target,cost from my_table',
    start_id,
    array(select distinct id::integer from (bla bla))
);
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • This solution works as I had intended it too. I realize now I tried implementing something similar but incorrectly so I disregarded it. Thanks for showing a good solution to a simple problem. – leffe86 Mar 17 '17 at 14:00