Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-docs-owner+M9194=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
	id 1d1b5z-0005gZ-Ri
	for pgsql-docs@arkaria.postgresql.org; Fri, 21 Apr 2017 16:07:43 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-docs-owner+M9194=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
	id 1d1b5y-0005K4-Kt
	for pgsql-docs@arkaria.postgresql.org; Fri, 21 Apr 2017 16:07:42 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <christophe.courtois@dalibo.com>)
	id 1d1b5b-0004g3-P8
	for pgsql-docs@postgresql.org; Fri, 21 Apr 2017 16:07:19 +0000
Received: from mimolette.dalibo.net ([212.85.157.144] helo=mail.dalibo.com)
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <christophe.courtois@dalibo.com>)
	id 1d1b5Y-0003V0-MT
	for pgsql-docs@postgresql.org; Fri, 21 Apr 2017 16:07:18 +0000
Received: from [192.168.45.24] (tu945-1-88-120-212-21.fbx.proxad.net
 [88.120.212.21])
	by mail.dalibo.com (Postfix) with ESMTPSA id 15F7C2C048D;
	Fri, 21 Apr 2017 18:07:14 +0200 (CEST)
Subject: Re: intagg.sgml: example wrongly named and does not compile
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <aa7cfd73-0d8d-596a-b684-39faa479afa5@dalibo.com>
 <15516.1492789516@sss.pgh.pa.us>
Cc: pgsql-docs@postgresql.org
From: Christophe Courtois <christophe.courtois@dalibo.com>
Organization: Dalibo
Message-ID: <ba54d8fa-603f-c789-2bc4-e6e86b7b01b2@dalibo.com>
Date: Fri, 21 Apr 2017 18:07:13 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: <15516.1492789516@sss.pgh.pa.us>
Content-Type: multipart/mixed;
 boundary="------------8B09A73EBA659D1E84A650EA"
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-docs>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-docs.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@postgresql.org>
List-Post: <mailto:pgsql-docs@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-docs>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
X-Mailing-List: pgsql-docs
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

This is a multi-part message in MIME format.
--------------8B09A73EBA659D1E84A650EA
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit

Le 21/04/2017 à 17:45, Tom Lane a écrit :
> Christophe Courtois <christophe.courtois@dalibo.com> writes:
>> I've found out that the example in intagg.sgml is wrongly named: the
>> one-to-many table is a many-to-many.
> Well, it'd depend on how it was used.  The example clearly intends that
> it be one-to-many, and I'm not sure it still makes sense without that
> restriction.  Maybe better to add a unique constraint on
> one_to_many(left)?

Perhaps the whole example can be simplified to get rid of the "left"
table, but I didn't intend to rewrite it.

>> And my colleague Thibaut Madeleine has seen that the "CREATE TABLE
>> right" and "CREATE TABLE left" examples cannot compile due to the
>> reserved words.
> Ouch.  Shows you how old this module is :-(

Indeed.

>> I propose the attached patch to fix that.
> Um, the attached file seems empty from here.

Ooops, sorry. It is attached.




-- 
Christophe Courtois
Consultant Dalibo
http://dalibo.com/  -  http://dalibo.org/

--------------8B09A73EBA659D1E84A650EA
Content-Type: text/x-patch;
 name="intagg.sgml.patch"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
 filename="intagg.sgml.patch"

--- intagg.sgml.HEAD	2016-11-17 09:41:53.173988630 +0100
+++ intagg.sgml	2017-04-21 16:29:10.421996579 +0200
@@ -54,20 +54,23 @@
   <title>Sample Uses</title>
=20
   <para>
-   Many database systems have the notion of a one to many table. Such a ta=
ble
+   Many database systems have the notion of a many to many table. Such a t=
able
    usually sits between two indexed tables, for example:
=20
 <programlisting>
-CREATE TABLE left (id INT PRIMARY KEY, ...);
-CREATE TABLE right (id INT PRIMARY KEY, ...);
-CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES ri=
ght);
+CREATE TABLE left_table  (id INT PRIMARY KEY, ...);
+CREATE TABLE right_table (id INT PRIMARY KEY, ...);
+CREATE TABLE many_to_many(id_left  INT REFERENCES left_table,=20
+                          id_right INT REFERENCES right_table);
 </programlisting>
=20
   It is typically used like this:
=20
 <programlisting>
-SELECT right.* from right JOIN one_to_many ON (right.id =3D one_to_many.ri=
ght)
-  WHERE one_to_many.left =3D <replaceable>item</>;
+SELECT right_table.*=20
+FROM right_table=20
+JOIN many_to_many ON (right_table.id =3D many_to_many.id_right)
+WHERE many_to_many.id_left =3D <replaceable>item</>;
 </programlisting>
=20
   This will return all the items in the right hand table for an entry
@@ -76,7 +79,7 @@
=20
  <para>
   Now, this methodology can be cumbersome with a very large number of
-  entries in the <structname>one_to_many</> table.  Often,
+  entries in the <structname>many_to_many</> table.  Often,
   a join like this would result in an index scan
   and a fetch for each right hand entry in the table for a particular
   left hand entry. If you have a very dynamic system, there is not much you
@@ -85,9 +88,9 @@
=20
 <programlisting>
 CREATE TABLE summary AS
-  SELECT left, int_array_aggregate(right) AS right
-  FROM one_to_many
-  GROUP BY left;
+  SELECT id_left, int_array_aggregate(id_right) AS rights
+  FROM many_to_many
+  GROUP BY id_left;
 </programlisting>
=20
   This will create a table with one row per left item, and an array
@@ -95,33 +98,35 @@
   the array; that's why there is an array enumerator.  You can do
=20
 <programlisting>
-SELECT left, int_array_enum(right) FROM summary WHERE left =3D <replaceabl=
e>item</>;
+SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left =3D <rep=
laceable>item</>;
 </programlisting>
=20
   The above query using <function>int_array_enum</> produces the same resu=
lts
   as
=20
 <programlisting>
-SELECT left, right FROM one_to_many WHERE left =3D <replaceable>item</>;
+SELECT id_left, id_right FROM many_to_many WHERE id_left =3D <replaceable>=
item</>;
 </programlisting>
=20
   The difference is that the query against the summary table has to get
   only one row from the table, whereas the direct query against
-  <structname>one_to_many</> must index scan and fetch a row for each entr=
y.
+  <structname>many_to_many</> must index scan and fetch a row for each ent=
ry.
  </para>
=20
  <para>
   On one system, an <command>EXPLAIN</> showed a query with a cost of 8488=
 was
   reduced to a cost of 329.  The original query was a join involving the
-  <structname>one_to_many</> table, which was replaced by:
+  <structname>many_to_many</> table, which was replaced by:
=20
 <programlisting>
-SELECT right, count(right) FROM
-  ( SELECT left, int_array_enum(right) AS right
-    FROM summary JOIN (SELECT left FROM left_table WHERE left =3D <replace=
able>item</>) AS lefts
-         ON (summary.left =3D lefts.left)
+SELECT id_right, count(id_right) FROM
+  ( SELECT id_left, int_array_enum(rights) AS id_right
+    FROM summary=20
+    JOIN (SELECT id FROM left_table=20
+          WHERE id =3D <replaceable>item</>) AS lefts
+    ON (summary.id_left =3D lefts.id)
   ) AS list
-  GROUP BY right
+  GROUP BY id_right
   ORDER BY count DESC;
 </programlisting>
  </para>

--------------8B09A73EBA659D1E84A650EA
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

--------------8B09A73EBA659D1E84A650EA--