I have a table paths:

CREATE TABLE paths (
    id_travel INT,
    point INT,
    visited INT
);

Sample rows:

 id_travel | point | visited 
----------- ------- ---------
        10 |   35  |       0
        10 |   16  |       1
        10 |   93  |       2
         5 |   15  |       0
         5 |   26  |       1
         5 |  193  |       2
         5 |   31  |       3

And another table distances:

CREATE TABLE distances (
    id_port1 INT,
    id_port2 INT,
    distance INT CHECK (distance > 0),
    PRIMARY KEY (id_port1, id_port2)
);

I need to make a view:

 id_travel | point1 | point2 | distance 
----------- -------- -------- ---------
        10 |   35   |     16 |  1568
        10 |   16   |     93 |  987
         5 |   15   |     26 |  251
         5 |   26   |    193 |  87
         5 |  193   |     31 |  356

I don't know how to make dist_trips by a recursive request here:

CREATE VIEW dist_view AS
WITH RECURSIVE dist_trips (id_travel, point1, point2) AS 
   (SELECT ????)
SELECT dt.id_travel, dt.point1, dt.point2, d.distance
FROM dist_trips dt
NATURAL JOIN distances d;

dist_trips is a recursive request witch and should return three columns: id_travel, point1, and point2 from table paths.

CodePudding user response：

You don't need recursion. Can be plain joins:

SELECT id_travel, p1.point AS point1, p2.point AS point2, d.distance 
FROM   paths p1
JOIN   paths p2 USING (id_travel)
LEFT   JOIN distances d ON d.id_port1 = p1.point
                       AND d.id_port2 = p2.point
WHERE  p2.visited = p1.visited   1
ORDER  BY id_travel, p1.visited;

db<>fiddle here

Your paths seem to have gapless ascending numbers. Just join each point with the next.

I threw in a LEFT JOIN to keep all edges of each path in the result, even if the distances table should not have a matching entry. Probably prudent.

Your NATURAL JOIN didn't go anywhere. Generally, NATURAL is rarely useful and breaks easily. The manual warns:

USING is reasonably safe from column changes in the joined relations since only the listed columns are combined. NATURAL is considerably more risky since any schema changes to either relation that cause a new matching column name to be present will cause the join to combine that new column as well.