diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/queries.sgml | 19 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 23 |
2 files changed, 33 insertions, 9 deletions
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 26bc2243843..9cffbd7071b 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.58 2010/07/03 02:57:46 rhaas Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.59 2010/08/07 02:44:05 tgl Exp $ --> <chapter id="queries"> <title>Queries</title> @@ -886,10 +886,7 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales In this example, the columns <literal>product_id</literal>, <literal>p.name</literal>, and <literal>p.price</literal> must be in the <literal>GROUP BY</> clause since they are referenced in - the query select list. (Depending on how the products - table is set up, name and price might be fully dependent on the - product ID, so the additional groupings could theoretically be - unnecessary, though this is not implemented.) The column + the query select list (but see below). The column <literal>s.units</> does not have to be in the <literal>GROUP BY</> list since it is only used in an aggregate expression (<literal>sum(...)</literal>), which represents the sales @@ -898,6 +895,18 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales </para> <para> + If the products table is set up so that, + say, <literal>product_id</literal> is the primary key, then it + would be enough to group by <literal>product_id</literal> in the + above example, since name and price would + be <firstterm>functionally + dependent</firstterm><indexterm><primary>functional + dependency</primary></indexterm> on the product ID, and so there + would be no ambiguity about which name and price value to return + for each product ID group. + </para> + + <para> In strict SQL, <literal>GROUP BY</> can only group by columns of the source table but <productname>PostgreSQL</productname> extends this to also allow <literal>GROUP BY</> to group by columns in the diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 15d9b75b59b..916146df737 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.131 2010/06/15 20:04:53 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.132 2010/08/07 02:44:05 tgl Exp $ PostgreSQL documentation --> @@ -520,9 +520,12 @@ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] produces a single value computed across all the selected rows). When <literal>GROUP BY</literal> is present, it is not valid for the <command>SELECT</command> list expressions to refer to - ungrouped columns except within aggregate functions, since there - would be more than one possible value to return for an ungrouped - column. + ungrouped columns except within aggregate functions or if the + ungrouped column is functionally dependent on the grouped columns, + since there would otherwise be more than one possible value to + return for an ungrouped column. A functional dependency exists if + the grouped columns (or a subset thereof) are the primary key of + the table containing the ungrouped column. </para> </refsect2> @@ -1591,6 +1594,18 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; </refsect2> <refsect2> + <title>Functional Dependencies</title> + + <para> + <productname>PostgreSQL</productname> recognizes functional dependency + (allowing columns to be omitted from <literal>GROUP BY</>) only when + a table's primary key is included in the <literal>GROUP BY</> list. + The SQL standard specifies additional conditions that should be + recognized. + </para> + </refsect2> + + <refsect2> <title><literal>WINDOW</literal> Clause Restrictions</title> <para> |