Postgres Pro
Downloads
Home   >   mailing lists

Thread: Handling of quantity in recursive query example

Handling of quantity in recursive query example

From
PG Doc comments form
Date:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/14/queries-with.html
Description:

I believe that the second query in section 7.8.2 (Recursive Queries) should
be modified to properly account for quantities. In the recursive term,
p.quantity should be multiplied by pr.quantity:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity * pr.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

As currently written, if a car has four wheels, and each wheel has five
bolts, the whole example returns five bolts for the parts of a car. With the
proposed change, it will return 20 bolts.

Re: Handling of quantity in recursive query example

From
Simon Riggs
Date:
On Sat, 27 Aug 2022 at 18:04, PG Doc comments form
<noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/queries-with.html
> Description:
>
> I believe that the second query in section 7.8.2 (Recursive Queries) should
> be modified to properly account for quantities. In the recursive term,
> p.quantity should be multiplied by pr.quantity:
>
> WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
>     SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
>   UNION ALL
>     SELECT p.sub_part, p.part, p.quantity * pr.quantity
>     FROM included_parts pr, parts p
>     WHERE p.part = pr.sub_part
> )
> SELECT sub_part, SUM(quantity) as total_quantity
> FROM included_parts
> GROUP BY sub_part
>
> As currently written, if a car has four wheels, and each wheel has five
> bolts, the whole example returns five bolts for the parts of a car. With the
> proposed change, it will return 20 bolts.

I agree, based on the attached test.

psql -f parts.sql

Existing
 sub_part | total_quantity
----------+----------------
 wheel    |              4
 bolt      |              5
(2 rows)

Proposed change
 sub_part | total_quantity
----------+----------------
 wheel    |              4
 bolt      |             20

Doc patch attached.

-- 
Simon Riggs                http://www.EnterpriseDB.com/
Attachment

Re: Handling of quantity in recursive query example

From
Tom Lane
Date:
Simon Riggs <simon.riggs@enterprisedb.com> writes:
> On Sat, 27 Aug 2022 at 18:04, PG Doc comments form
> <noreply@postgresql.org> wrote:
>> As currently written, if a car has four wheels, and each wheel has five
>> bolts, the whole example returns five bolts for the parts of a car. With the
>> proposed change, it will return 20 bolts.

> I agree, based on the attached test.
> ...
> Doc patch attached.

Pushed, thanks.

            regards, tom lane