Move path_length to schema as view.

2 files changed, 7 insertions, 9 deletions

diff --git a/domagi/path-depth.sql b/domagi/path-depth.sql
index 081d015..f4d33d3 100644
--- a/domagi/path-depth.sql
+++ b/domagi/path-depth.sql
@@ -9,15 +9,6 @@ WITH path_depth AS (
        INNER JOIN path ON path.id=path_segment.path_id
        -- subset paths
        WHERE ($1 IS NULL) OR (path.name IN (SELECT UNNEST($1)))
-       GROUP BY path_id),
-     path_length AS (
-       -- Compute length of each path.
-       SELECT path_id, sum(len(sequence)) AS length
-       FROM path_segment
-       INNER JOIN segment ON segment_id=segment.id
-       INNER JOIN path ON path.id=path_segment.path_id
-       -- subset paths (same filter as above)
-       WHERE (($1 IS NULL) OR (path.name IN (SELECT UNNEST($1))))
        GROUP BY path_id)
     -- Combine path name, length and mean depth for display.
     SELECT name, length, mean_depth
diff --git a/domagi/schema.sql b/domagi/schema.sql
index 29066f9..f9a867f 100644
--- a/domagi/schema.sql
+++ b/domagi/schema.sql
@@ -28,6 +28,13 @@ CREATE TABLE path_segment (
        "end" INTEGER,
 );
 
+-- One-to-one relation mapping path IDs to their length
+CREATE VIEW path_length AS
+  SELECT ANY_VALUE(path_id) AS path_id, sum(len(sequence))::INTEGER AS length
+  FROM path_segment
+  INNER JOIN segment ON segment.id=path_segment.segment_id
+  GROUP BY path_id;
+
 -- One-to-one relation mapping segment IDs to their depth
 CREATE VIEW segment_depth AS
   WITH segment_depth_nonzero_depths_only AS (