2

I have a graph modelled in a table. Each row is an edge and contains two ints, being the from and to vertices.

Given a starting edge, I want to identify the edges which are not walkable - i.e. disconnected edges.

I'd like to do this with a loop function in Postgres, but I'm not sure how to manipulate the arrays while looping.

This is what I have so far. The string literals ending in ??? are the expressions I'm not sure how to do. Additionally, I've not created functions or used loops in PL/pgSQL before so I might have bad assumptions about how they work.

create or replace function find_connected_links(from_link_id int8)
  returns table (link_id int8)
  language plpgsql
  as $$
    declare
      found_link_ids int8[];
      examine_link_ids int8[] = ARRAY[from_link_id];
    begin
      while array_length(examine_link_ids, 1) > 0 LOOP
        found_link_ids := array_prepend(
            'get first element from examine_link_ids array ???', found_link_ids);
        examine_link_ids := array_cat(
            'get tail of examine_link_ids array ??? ',
            'get a new array of link_ids that are connected but not already in found_link_ids or examine_link_ids ???');
        return query select 'get first element from examine_link_ids array ???';
      end LOOP
  end $$
Synesso
  • 37,610
  • 35
  • 136
  • 207
  • This link may help to answer some of your questions. http://stackoverflow.com/questions/25754366/postgresql-recursive-query-challenge-simple-parent-child-example – ramana_k Sep 24 '15 at 06:32
  • It would be instrumental to include the necessary basics for recreating your case: Postgres version (*always*), table definition (what you get with `\d tbl` in psql or a complete `CREATE TABLE` script), some sample values, expected result. – Erwin Brandstetter Sep 27 '15 at 22:38

0 Answers0