[SOLVED] how to use a loop with the function pgr

how to use a loop with the function pgr_dijkstra

I want to use a loop to calculate the distance traveled between two nodes 152 and 17720 (I use the function pgr_dijkstra) by deleting each time a cell. A cell contains several road links. the grid_edges_routard table contains the road links and the corresponding cell. Iwant to have for each blocked cell the distance traveled between the two nodes. I must use pgr_dijkstra to display in a second time the links traveled.

CREATE OR REPLACE FUNCTION get_dist_grid() 
 RETURNS TABLE (
 celref_blocked INT,
 dist INT
) AS $$
DECLARE 
    var_r record;
BEGIN
 FOR var_r IN(SELECT distinct(cellule)as cel from grid_edges_routard )  
 LOOP

 SELECT * FROM pgr_dijkstra('SELECT id, source, target,cost 
                    FROM road_routard.edges_vulnerabilite 
                    where id not in (select edge_id
                                                     from grid_edges_routard
                                                     where cellule=var_r)  ',152   ,17720, FALSE)
                 where edge=-1; 
     celref_blocked  := var_r.cel ;            

        RETURN NEXT;
 END LOOP;
END; $$ 
LANGUAGE 'plpgsql';

select get_dist_grid()

I have an error message: ERROR: column « var_r » does not exist. I use postgresql 9.5.

Solution

Define a new variable (var_q) of type record, then Execute your select query into your defined variable like this Execute 'SELECT * FROM pgr_dijkstra(''SELECT id, source, target,cost FROM road_routard.edges_vulnerabilite where id not in (select edge_id from grid_edges_routard where cellule='||var_r||') '',152 ,17720, FALSE) where edge=-1' into var_q This might give some errors as we have to escape the quotes for inner query, Try escaping quotes if it doesn't work and then you can use the out of the query in similar way as you have used celref_blocked := var_r.cel