CODEWITHSUNDEEP
arrayspostgresqlpostgisdijkstra
leffe86
leffe86

Reputation: 13

PostgreSQL: Insert dynamic ARRAY into functions

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

Upvotes: 1

Views: 2577

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

select * 
from pgr_dijkstra(
    'select id,source,target,cost from my_table',
    start_id,
    array(select distinct id::integer from (bla bla))
);

Upvotes: 1

Related Questions