pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedHandling of quantity in recursive query example
3+ messages / 3 participants
[nested] [flat]
* Handling of quantity in recursive query example
@ 2022-08-27 14:50 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2022-08-27 14:50 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: jason@banfelder.net
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Handling of quantity in recursive query example
@ 2022-08-28 11:43 Simon Riggs <simon.riggs@enterprisedb.com>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Simon Riggs @ 2022-08-28 11:43 UTC (permalink / raw)
To: jason@banfelder.net; pgsql-docs@lists.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.sgml
index 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
)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Handling of quantity in recursive query example
@ 2022-08-28 14:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon.riggs@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-08-28 14:45 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: jason@banfelder.net; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-08-28 14:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-27 14:50 Handling of quantity in recursive query example PG Doc comments form <noreply@postgresql.org>
2022-08-28 11:43 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-28 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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