On Thu, Mar 27, 2025 at 12:37:53PM -0400, Robert Haas wrote:
> A recursive CTE effectively takes two queries that will be run as
> arguments. For some reason, instead of choosing syntax like WITH
> RECURSIVE t1 AS BASE_CASE (initial_query) RECURSIVE_CASE
> (iterated_query), somebody chose WITH RECU
On Fri, Mar 28, 2025 at 1:14 AM Tom Lane wrote:
>
> Well, we extend the spec in lots of places. I'd be okay with removing
> this restriction if I were sure there were no bad consequences, but
> it seems likely that there are some. College math was way too long
> ago for me to be sure about the "
Renan Alves Fonseca writes:
> I suspected that this restriction came straight from the specs. I
> understand that while the proposed solution can help in some specific
> use cases, it is not enough to justify an exception to the spec.
Well, we extend the spec in lots of places. I'd be okay with
On Thu, Mar 27, 2025 at 10:50 PM Tom Lane wrote:
>
> Renan Alves Fonseca writes:
> > The solution using GROUP BY in the recursive query is the following:
>
> > with recursive t1(node,nb_path) as
> > (select 1,1::numeric
> >union all
> > (select dag.target, sum(nb_path)
> >
Renan Alves Fonseca writes:
> The solution using GROUP BY in the recursive query is the following:
> with recursive t1(node,nb_path) as
> (select 1,1::numeric
>union all
> (select dag.target, sum(nb_path)
> from t1 join dag on t1.node=dag.source
> group by 1)
>
On Thu, Mar 27, 2025 at 7:32 PM Robert Haas wrote:
>
> > 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,
> inste
Renan Alves Fonseca writes:
> I'll assume that the silence about allowing GROUP BY means it is not a
> great idea...
Well, you can do grouping, ordering, or whatever else you want to the
result of the recursive WITH in the outer query level. I don't see
any advantage in allowing an additional le
On Thu, Mar 27, 2025 at 2:21 PM Renan Alves Fonseca
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?
--
On Thu, Mar 27, 2025 at 7:10 PM David G. Johnston
wrote:
>
> There is distinct behavior between group by and order by here. You seem to
> be mixing them up.
>
> From Select:
>
> select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO
> KEY UPDATE, FOR UPDATE, FOR SHARE, or F
On Thu, Mar 27, 2025 at 11:03 AM Renan Alves Fonseca
wrote:
> On Thu, Mar 27, 2025 at 5:38 PM Robert Haas wrote:
> > It's not a problem if UNION ALL is used within the initial_query and
> > it's not a problem if UNION ALL is used within the iterated_query. But
> > you can't apply ORDER BY to the
On Thu, Mar 27, 2025 at 5:38 PM Robert Haas wrote:
> It's not a problem if UNION ALL is used within the initial_query and
> it's not a problem if UNION ALL is used within the iterated_query. But
> you can't apply ORDER BY to the result of the UNION, because the UNION
> is kind of fake -- we're not
On Thu, Mar 27, 2025 at 9:02 AM Renan Alves Fonseca
wrote:
> So, do we support the ORDER BY in a recursive query or not?
>
Apparently we do. The "in" recurses.
If the answer is yes, I suggest one of the following modifications:
>
1. Change the error message to something like "ORDER BY at the t
On Thu, Mar 27, 2025 at 12:37 PM Robert Haas wrote:
> It's not a problem if UNION ALL is used within the initial_query and
> it's not a problem if UNION ALL is used within the iterated_query. But
> you can't apply ORDER BY to the result of the UNION, because the UNION
> is kind of fake -- we're no
On Thu, Mar 27, 2025 at 12:02 PM Renan Alves Fonseca
wrote:
> WITH RECURSIVE t1 AS ( SELECT 1 UNION SELECT generate_series(2,3) FROM t1
> ORDER BY 1 DESC) SELECT * FROM t1 ;
>
> The parser attaches the "order by" clause to the "union" operator, and then
> we error out with the following messag
14 matches
Mail list logo
