I am sorry but I still don't understand why it doesn't work. Possibly I misunderstand how with recursive works?
In my opinion,
with recursive table as{
seed statement
union
recursive statement
}
In every iteration, It will just generate results from seed statement union recursive statement and put them into a new temporary table, and then compare the results with the former temporary table and check if there are any new tuples. If no new tuples, just stop iteration. Is there any tricky things about recursive statement?
Thank you very much:)
Best,
Jing
On Wed, Nov 6, 2013 at 2:13 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:
Jing Fan wrote:You forget that the grouping query only spans the second branch
> If the grouping inside CTE is executed, I don't think it would generate result like
>
> src_id | dest_id | dist
> --------+---------+------
> 3384 | 6236 | 1
> 3384 | 1739 | 2
> 3384 | 6236 | 3
> 3384 | 1739 | 4
> 3384 | 6236 | 5
> 3384 | 1739 | 6
> 3384 | 6236 | 7
> 3384 | 1739 | 8
> 3384 | 6236 | 9
> 3384 | 1739 | 10
> 3384 | 6236 | 11
>
>
>
> for we have min(dist),
> so it should be like
>
>
> src_id | dest_id | dist
> --------+---------+------
> 3384 | 6236 | 1
> 3384 | 1739 | 2
>
>
>
> other values will be eliminated by min(). It actually generate no new tuples and the iteration should
> stop.
of the UNION, where you add the new entries.
So the new entries and the old entries won't be grouped together,
and the new paths that are longer than the old ones won't get removed.
Unfortunately you cannot have the UNION in a subquery for
recursive CTEs, but you could use arrays to achieve what you want:
WITH RECURSIVE paths (path) AS (
SELECT ARRAY[src_id, dest_id] FROM edge
UNION ALL
SELECT edge.src_id || paths.path
FROM paths, edge
WHERE edge.dest_id = paths.path[array_lower(paths.path, 1)]
AND edge.src_id <> ALL (paths.path)
)
SELECT path[1], path[array_upper(path, 1)], min(array_length(path, 1))
FROM paths
GROUP BY 1, 2;
The whole exercise sounds a bit like homework to me.
Yours,
Laurenz Albe
