Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit108fe47

Browse files
committed
Aggregate functions now support multiple input arguments. I also took
the opportunity to treat COUNT(*) as a zero-argument aggregate insteadof the old hack that equated it to COUNT(1); this is materially cleaner(no more weird ANYOID cases) and ought to be at least a tiny bit faster.Original patch by Sergey Koposov; review, documentation, simple regressiontests, pg_dump and psql support by moi.
1 parentc2d1138 commit108fe47

39 files changed

+702
-484
lines changed

‎doc/src/sgml/ref/alter_aggregate.sgml‎

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_aggregate.sgml,v 1.7 2005/10/13 22:44:51 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_aggregate.sgml,v 1.8 2006/07/27 19:52:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,9 +20,9 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) RENAME TO <replaceable>new_name</replaceable>
24-
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) OWNER TO <replaceable>new_owner</replaceable>
25-
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) SET SCHEMA <replaceable>new_schema</replaceable>
23+
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable>[ , ... ]) RENAME TO <replaceable>new_name</replaceable>
24+
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable>[ , ... ]) OWNER TO <replaceable>new_owner</replaceable>
25+
ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable>[ , ... ]) SET SCHEMA <replaceable>new_schema</replaceable>
2626
</synopsis>
2727
</refsynopsisdiv>
2828

@@ -64,8 +64,9 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable
6464
<term><replaceable class="parameter">type</replaceable></term>
6565
<listitem>
6666
<para>
67-
The argument data type of the aggregate function, or
68-
<literal>*</literal> if the function accepts any data type.
67+
An input data type on which the aggregate function operates.
68+
To reference a zero-argument aggregate function, write <literal>*</>
69+
in place of the list of input data types.
6970
</para>
7071
</listitem>
7172
</varlistentry>

‎doc/src/sgml/ref/comment.sgml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.30 2006/02/12 03:22:17 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.31 2006/07/27 19:52:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,7 +24,7 @@ COMMENT ON
2424
{
2525
TABLE <replaceable class="PARAMETER">object_name</replaceable> |
2626
COLUMN <replaceable class="PARAMETER">table_name</replaceable>.<replaceable class="PARAMETER">column_name</replaceable> |
27-
AGGREGATE <replaceable class="PARAMETER">agg_name</replaceable> (<replaceable class="PARAMETER">agg_type</replaceable>) |
27+
AGGREGATE <replaceable class="PARAMETER">agg_name</replaceable> (<replaceable class="PARAMETER">agg_type</replaceable> [, ...]) |
2828
CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>) |
2929
CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
3030
CONVERSION <replaceable class="PARAMETER">object_name</replaceable> |
@@ -101,8 +101,9 @@ COMMENT ON
101101
<term><replaceable class="parameter">agg_type</replaceable></term>
102102
<listitem>
103103
<para>
104-
The argument data type of the aggregate function, or
105-
<literal>*</literal> if the function accepts any data type.
104+
An input data type on which the aggregate function operates.
105+
To reference a zero-argument aggregate function, write <literal>*</>
106+
in place of the list of input data types.
106107
</para>
107108
</listitem>
108109
</varlistentry>

‎doc/src/sgml/ref/create_aggregate.sgml‎

Lines changed: 34 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.34 2006/04/15 17:45:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.35 2006/07/27 19:52:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">input_data_type</replaceable> ) (
23+
CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">input_data_type</replaceable>[ , ... ]) (
2424
SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
2525
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
2626
[ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
@@ -60,35 +60,36 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
6060
</para>
6161

6262
<para>
63-
Anaggregatefunction is identified by its name and input data type.
63+
An aggregate function is identified by its name and input data type(s).
6464
Two aggregates in the same schema can have the same name if they operate on
6565
different input types. The
66-
name and input data type of an aggregate must also be distinct from
66+
name and input data type(s) of an aggregate must also be distinct from
6767
the name and input data type(s) of every ordinary function in the same
6868
schema.
6969
</para>
7070

7171
<para>
72-
Anaggregate function is made from one or two ordinary
72+
An aggregate function is made from one or two ordinary
7373
functions:
7474
a state transition function
7575
<replaceable class="PARAMETER">sfunc</replaceable>,
7676
and an optional final calculation function
7777
<replaceable class="PARAMETER">ffunc</replaceable>.
7878
These are used as follows:
7979
<programlisting>
80-
<replaceable class="PARAMETER">sfunc</replaceable>( internal-state, next-data-item ) ---> next-internal-state
80+
<replaceable class="PARAMETER">sfunc</replaceable>( internal-state, next-data-values ) ---> next-internal-state
8181
<replaceable class="PARAMETER">ffunc</replaceable>( internal-state ) ---> aggregate-value
8282
</programlisting>
8383
</para>
8484

8585
<para>
8686
<productname>PostgreSQL</productname> creates a temporary variable
8787
of data type <replaceable class="PARAMETER">stype</replaceable>
88-
to hold the current internal state of the aggregate. At each input
89-
data item,
90-
the state transition function is invoked to calculate a new
91-
internal state value. After all the data has been processed,
88+
to hold the current internal state of the aggregate. At each input row,
89+
the aggregate argument value(s) are calculated and
90+
the state transition function is invoked with the current state value
91+
and the new argument value(s) to calculate a new
92+
internal state value. After all the rows have been processed,
9293
the final function is invoked once to calculate the aggregate's return
9394
value. If there is no final function then the ending state value
9495
is returned as-is.
@@ -106,23 +107,24 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
106107
<para>
107108
If the state transition function is declared <quote>strict</quote>,
108109
then it cannot be called with null inputs. With such a transition
109-
function, aggregate execution behaves as follows. Null input values
110-
are ignored (the function is not called and the previous state value
111-
is retained). If the initial state value is null, then the first
112-
nonnull input value replaces the state value, and the transition
113-
function is invoked beginning with the second nonnull input value.
110+
function, aggregate execution behaves as follows. Rows with any null input
111+
values are ignored (the function is not called and the previous state value
112+
is retained). If the initial state value is null, then at the first row
113+
with all-nonnull input values, the first argument value replaces the state
114+
value, and the transition function is invoked at subsequent rows with
115+
all-nonnull input values.
114116
This is handy for implementing aggregates like <function>max</function>.
115117
Note that this behavior is only available when
116118
<replaceable class="PARAMETER">state_data_type</replaceable>
117-
is the same as
119+
is the same as the first
118120
<replaceable class="PARAMETER">input_data_type</replaceable>.
119121
When these types are different, you must supply a nonnull initial
120122
condition or use a nonstrict transition function.
121123
</para>
122124

123125
<para>
124126
If the state transition function is not strict, then it will be called
125-
unconditionally at each inputvalue, and must deal with null inputs
127+
unconditionally at each inputrow, and must deal with null inputs
126128
and null transition values for itself. This allows the aggregate
127129
author to have full control over the aggregate's handling of null values.
128130
</para>
@@ -180,10 +182,10 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
180182
<term><replaceable class="PARAMETER">input_data_type</replaceable></term>
181183
<listitem>
182184
<para>
183-
The input data type on which this aggregate function operates.
184-
This can be specified as<literal>*</> for an aggregate that
185-
does not examine itsinputvalues (an exampleis
186-
<function>count(*)</function>).
185+
An input data type on which this aggregate function operates.
186+
To create a zero-argument aggregate function, write<literal>*</>
187+
in place of the list ofinputdata types. (An exampleof such an
188+
aggregate is<function>count(*)</function>.)
187189
</para>
188190
</listitem>
189191
</varlistentry>
@@ -195,8 +197,8 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
195197
In the old syntax for <command>CREATE AGGREGATE</>, the input data type
196198
is specified by a <literal>basetype</> parameter rather than being
197199
written next to the aggregate name. Note that this syntax allows
198-
only one input parameter. To defineanaggregatethat does not examine
199-
its input values,specify the <literal>basetype</> as
200+
only one input parameter. To definea zero-argumentaggregatefunction,
201+
specify the <literal>basetype</> as
200202
<literal>"ANY"</> (not <literal>*</>).
201203
</para>
202204
</listitem>
@@ -207,17 +209,15 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
207209
<listitem>
208210
<para>
209211
The name of the state transition function to be called for each
210-
input data value. This is normally a function of two arguments,
212+
input row. For an <replaceable class="PARAMETER">N</>-argument
213+
aggregate function, the <replaceable class="PARAMETER">sfunc</>
214+
must take <replaceable class="PARAMETER">N</>+1 arguments,
211215
the first being of type <replaceable
212-
class="PARAMETER">state_data_type</replaceable> and the second
213-
of type <replaceable
214-
class="PARAMETER">input_data_type</replaceable>. Alternatively,
215-
for an aggregate that does not examine its input values, the
216-
function takes just one argument of type <replaceable
217-
class="PARAMETER">state_data_type</replaceable>. In either case
218-
the function must return a value of type <replaceable
216+
class="PARAMETER">state_data_type</replaceable> and the rest
217+
matching the declared input data type(s) of the aggregate.
218+
The function must return a value of type <replaceable
219219
class="PARAMETER">state_data_type</replaceable>. This function
220-
takes the current state value and the current input dataitem,
220+
takes the current state value and the current input datavalue(s),
221221
and returns the next state value.
222222
</para>
223223
</listitem>
@@ -237,7 +237,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
237237
<listitem>
238238
<para>
239239
The name of the final function called to compute the aggregate's
240-
result after all inputdata has been traversed. The function
240+
result after all inputrows have been traversed. The function
241241
must take a single argument of type <replaceable
242242
class="PARAMETER">state_data_type</replaceable>. The return
243243
data type of the aggregate is defined as the return type of this
@@ -269,7 +269,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
269269
<function>MAX</>-like aggregate.
270270
This is just an operator name (possibly schema-qualified).
271271
The operator is assumed to have the same input data types as
272-
the aggregate.
272+
the aggregate (which must be a single-argument aggregate).
273273
</para>
274274
</listitem>
275275
</varlistentry>

‎doc/src/sgml/ref/drop_aggregate.sgml‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_aggregate.sgml,v 1.28 2006/06/16 22:27:55 adunstan Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_aggregate.sgml,v 1.29 2006/07/27 19:52:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">type</replaceable> ) [ CASCADE | RESTRICT ]
23+
DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">type</replaceable>[ , ... ]) [ CASCADE | RESTRICT ]
2424
</synopsis>
2525
</refsynopsisdiv>
2626

@@ -43,7 +43,7 @@ DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> (
4343
<term><literal>IF EXISTS</literal></term>
4444
<listitem>
4545
<para>
46-
Do not throw an error if the aggregate does not exist. A notice is issued
46+
Do not throw an error if the aggregate does not exist. A notice is issued
4747
in this case.
4848
</para>
4949
</listitem>
@@ -62,8 +62,9 @@ DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> (
6262
<term><replaceable class="parameter">type</replaceable></term>
6363
<listitem>
6464
<para>
65-
The argument data type of the aggregate function, or
66-
<literal>*</literal> if the function accepts any data type.
65+
An input data type on which the aggregate function operates.
66+
To reference a zero-argument aggregate function, write <literal>*</>
67+
in place of the list of input data types.
6768
</para>
6869
</listitem>
6970
</varlistentry>

‎doc/src/sgml/ref/psql-ref.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.165 2006/05/31 22:34:35 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.166 2006/07/27 19:52:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -854,7 +854,7 @@ testdb=&gt;
854854
<listitem>
855855
<para>
856856
Lists all available aggregate functions, together with the data
857-
type they operate on. If <replaceable
857+
types they operate on. If <replaceable
858858
class="parameter">pattern</replaceable>
859859
is specified, only aggregates whose names match the pattern are shown.
860860
</para>

‎doc/src/sgml/sql.sgml‎

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.40 2006/04/30 18:30:38 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.41 2006/07/27 19:52:04 tgl Exp $ -->
22

33
<chapter id="sql-intro">
44
<title>SQL</title>
@@ -1247,13 +1247,13 @@ select sname, pname from supplier
12471247
</sect3>
12481248

12491249
<sect3>
1250-
<title id="aggregates-tutorial">AggregateOperators</title>
1250+
<title id="aggregates-tutorial">AggregateFunctions</title>
12511251

12521252
<para>
1253-
<acronym>SQL</acronym> provides aggregateoperators (e.g. AVG,
1254-
COUNT, SUM, MIN, MAX) that take an expression as argument. The
1255-
expression is evaluated at each row that satisfies the WHERE
1256-
clause, and the aggregateoperator is calculated over this set
1253+
<acronym>SQL</acronym> provides aggregatefunctions such as AVG,
1254+
COUNT, SUM, MIN,andMAX. The argument(s) of an aggregate function
1255+
are evaluated at each row that satisfies the WHERE
1256+
clause, and the aggregatefunction is calculated over this set
12571257
of input values. Normally, an aggregate delivers a single
12581258
result for a whole <command>SELECT</command> statement. But if
12591259
grouping is specified in the query, then a separate calculation
@@ -1311,10 +1311,10 @@ SELECT COUNT(PNO)
13111311
<para>
13121312
<acronym>SQL</acronym> allows one to partition the tuples of a table
13131313
into groups. Then the
1314-
aggregateoperators described above can be applied to the groups &mdash;
1315-
i.e. the value of the aggregateoperator is no longer calculated over
1314+
aggregatefunctions described above can be applied to the groups &mdash;
1315+
i.e. the value of the aggregatefunction is no longer calculated over
13161316
all the values of the specified column but over all values of a
1317-
group. Thus the aggregateoperator is evaluated separately for every
1317+
group. Thus the aggregatefunction is evaluated separately for every
13181318
group.
13191319
</para>
13201320

@@ -1396,17 +1396,17 @@ SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
13961396

13971397
<para>
13981398
In our example we got four groups and now we can apply the aggregate
1399-
operator COUNT to every group leading to the final result of the query
1399+
function COUNT to every group leading to the final result of the query
14001400
given above.
14011401
</para>
14021402
</example>
14031403
</para>
14041404

14051405
<para>
14061406
Note that for a query using GROUP BY and aggregate
1407-
operators to make sense the target list can only refer directly to
1407+
functions to make sense, the target list can only refer directly to
14081408
the attributes being grouped by. Other attributes may only be used
1409-
inside theargument ofanaggregatefunction. Otherwise there would
1409+
inside thearguments of aggregatefunctions. Otherwise there would
14101410
not be a unique value to associate with the other attributes.
14111411
</para>
14121412

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp