Re: Handling of quantity in recursive query example

On Sat, 27 Aug 2022 at 18:04, PG Doc comments form
<noreply(at)postgresql(dot)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/