redirect-list.sql

CREATE OR REPLACE VIEW redirect_list AS
  WITH RECURSIVE recursive_url_relationships (job_id, source, source_url, target, target_url, redirect) AS
         (SELECT a.job_id,
                 a.source,
                 b.value AS source_url,
                 a.target,
                 c.value AS target_url,
                 a.redirect
            FROM url_relationships AS a
            JOIN urls AS b ON b.id = a.source
            JOIN urls AS c ON c.id = a.target
           UNION SELECT s.job_id,
                        s.source,
                        d.value AS souce_url,
                        s.target,
                        e.value AS target_url,
                        s.redirect
                   FROM recursive_url_relationships AS t
                   JOIN url_relationships AS s
                     ON s.source = t.target
                   JOIN urls AS d
                     ON d.id = t.source
                   JOIN urls AS e
                     ON e.id = t.target)
    SELECT job_id::uuid, source, source_url::text, target, target_url::text, redirect::int
      FROM recursive_url_relationships
     UNION SELECT a.job_id,
                  NULL AS source,
                  NULL AS source_url,
                  a.target,
                  b.value AS target_url,
                  a.redirect
             FROM url_relationships AS a
             JOIN urls AS b ON b.id = a.target
            WHERE source IS NULL AND redirect = 0
     ORDER BY job_id, redirect;