From: Simon Riggs <simon.riggs@enterprisedb.com>
To: jason@banfelder.net
To: pgsql-docs@lists.postgresql.org
Subject: Re: Handling of quantity in recursive query example
Date: Sun, 28 Aug 2022 12:43:22 +0100
Message-ID: <CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB+g@mail.gmail.com> (raw)
In-Reply-To: <166161184718.1235920.6304070286124217754@wrigleys.postgresql.org>
References: <166161184718.1235920.6304070286124217754@wrigleys.postgresql.org>
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/Attachments:
[application/octet-stream] parts.sql (947B, ../CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB+g@mail.gmail.com/2-parts.sql)
download
[application/octet-stream] recursive_correction.v1.patch (533B, ../CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB+g@mail.gmail.com/3-recursive_correction.v1.patch)
download | inline diff:diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgmlindex 516fbcbf37..1428d99d0f 100644--- a/doc/src/sgml/queries.sgml+++ b/doc/src/sgml/queries.sgml@@ -2195,7 +2195,7 @@ SELECT sum(n) FROM t;
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+ SELECT p.sub_part, p.part, p.quantity * pr.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: simon.riggs@enterprisedb.com, jason@banfelder.net, pgsql-docs@lists.postgresql.org
Subject: Re: Handling of quantity in recursive query example
In-Reply-To: <CANbhV-Fg9j3LKHTr8PRfSnkWBwtUg1n5Wo0vs43BtGrUyaCB+g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox