Re: Remove restrictions in recursive query

On Thu, Mar 27, 2025 at 2:21 PM Renan Alves Fonseca
<renanfonseca(at)gmail(dot)com> wrote:
> You're right. I'm really mixing these 2 here. Thanks for the clarification.

It looks like GROUP BY binds to the particular UNION branch but ORDER
BY binds to the UNION as a whole:

robert.haas=# select 2 union all select 1;
?column?
----------
2
1
(2 rows)

robert.haas=# select 2 union all select 1 order by 1;
?column?
----------
1
2
(2 rows)

robert.haas=# select 2 union all select 1 group by 1;
?column?
----------
2
1
(2 rows)

> I'll assume that the silence about allowing GROUP BY means it is not a
> great idea...

I don't think there's really anything to keep you from doing this --
just put the grouping operation where you refer to the recursive CTE,
instead of inside the recursive CTE itself. I think allowing it to
appear inside the recursive CTE would be rather confusing -- it's
probably best if the mandatory UNION operation is at the top level.

--
Robert Haas
EDB: http://www.enterprisedb.com