Using Recursive SQL to get model element tree

[rburghol]

Get All Children in the Element Tree

• Example: riverseg BS1_8730_8540, with elid: 245877

\set elid 245877
\set include_children 1


BEGIN;

create temp table ptemp as select * from map_model_linkages limit 0;

WITH RECURSIVE element_tree AS (
      SELECT p.*
      FROM map_model_linkages as p 
      WHERE p.dest_id = :elid 
        AND linktype = 1 
      UNION
      SELECT c.*
      FROM map_model_linkages as c 
      inner join element_tree as p
      on (c.dest_id = p.src_id and c.linktype = 1 and :include_children = 1)
    )
SELECT * from element_tree;


COMMIT;