1--
2-- CREATE_AGGREGATE
3--
4
5-- all functions CREATEd
6CREATE AGGREGATE newavg (
7   sfunc = int4_avg_accum, basetype = int4, stype = _int8,
8   finalfunc = int8_avg,
9   initcond1 = '{0,0}'
10);
11
12-- test comments
13COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment';
14COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment';
15COMMENT ON AGGREGATE newavg (int4) IS NULL;
16
17-- without finalfunc; test obsolete spellings 'sfunc1' etc
18CREATE AGGREGATE newsum (
19   sfunc1 = int4pl, basetype = int4, stype1 = int4,
20   initcond1 = '0'
21);
22
23-- zero-argument aggregate
24CREATE AGGREGATE newcnt (*) (
25   sfunc = int8inc, stype = int8,
26   initcond = '0', parallel = safe
27);
28
29-- old-style spelling of same (except without parallel-safe; that's too new)
30CREATE AGGREGATE oldcnt (
31   sfunc = int8inc, basetype = 'ANY', stype = int8,
32   initcond = '0'
33);
34
35-- aggregate that only cares about null/nonnull input
36CREATE AGGREGATE newcnt ("any") (
37   sfunc = int8inc_any, stype = int8,
38   initcond = '0'
39);
40
41COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail';
42COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment';
43COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment';
44
45-- multi-argument aggregate
46create function sum3(int8,int8,int8) returns int8 as
47'select $1 + $2 + $3' language sql strict immutable;
48
49create aggregate sum2(int8,int8) (
50   sfunc = sum3, stype = int8,
51   initcond = '0'
52);
53
54-- multi-argument aggregates sensitive to distinct/order, strict/nonstrict
55create type aggtype as (a integer, b integer, c text);
56
57create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[]
58as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
59language sql strict immutable;
60
61create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[]
62as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
63language sql immutable;
64
65create aggregate aggfstr(integer,integer,text) (
66   sfunc = aggf_trans, stype = aggtype[],
67   initcond = '{}'
68);
69
70create aggregate aggfns(integer,integer,text) (
71   sfunc = aggfns_trans, stype = aggtype[], sspace = 10000,
72   initcond = '{}'
73);
74
75-- variadic aggregate
76create function least_accum(anyelement, variadic anyarray)
77returns anyelement language sql as
78  'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
79
80create aggregate least_agg(variadic items anyarray) (
81  stype = anyelement, sfunc = least_accum
82);
83
84-- test ordered-set aggs using built-in support functions
85create aggregate my_percentile_disc(float8 ORDER BY anyelement) (
86  stype = internal,
87  sfunc = ordered_set_transition,
88  finalfunc = percentile_disc_final,
89  finalfunc_extra = true
90);
91
92create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") (
93  stype = internal,
94  sfunc = ordered_set_transition_multi,
95  finalfunc = rank_final,
96  finalfunc_extra = true,
97  hypothetical
98);
99
100alter aggregate my_percentile_disc(float8 ORDER BY anyelement)
101  rename to test_percentile_disc;
102alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any")
103  rename to test_rank;
104
105\da test_*
106
107-- moving-aggregate options
108
109CREATE AGGREGATE sumdouble (float8)
110(
111    stype = float8,
112    sfunc = float8pl,
113    mstype = float8,
114    msfunc = float8pl,
115    minvfunc = float8mi
116);
117
118-- aggregate combine and serialization functions
119
120-- can't specify just one of serialfunc and deserialfunc
121CREATE AGGREGATE myavg (numeric)
122(
123	stype = internal,
124	sfunc = numeric_avg_accum,
125	serialfunc = numeric_avg_serialize
126);
127
128-- serialfunc must have correct parameters
129CREATE AGGREGATE myavg (numeric)
130(
131	stype = internal,
132	sfunc = numeric_avg_accum,
133	serialfunc = numeric_avg_deserialize,
134	deserialfunc = numeric_avg_deserialize
135);
136
137-- deserialfunc must have correct parameters
138CREATE AGGREGATE myavg (numeric)
139(
140	stype = internal,
141	sfunc = numeric_avg_accum,
142	serialfunc = numeric_avg_serialize,
143	deserialfunc = numeric_avg_serialize
144);
145
146-- ensure combine function parameters are checked
147CREATE AGGREGATE myavg (numeric)
148(
149	stype = internal,
150	sfunc = numeric_avg_accum,
151	serialfunc = numeric_avg_serialize,
152	deserialfunc = numeric_avg_deserialize,
153	combinefunc = int4larger
154);
155
156-- ensure create aggregate works.
157CREATE AGGREGATE myavg (numeric)
158(
159	stype = internal,
160	sfunc = numeric_avg_accum,
161	finalfunc = numeric_avg,
162	serialfunc = numeric_avg_serialize,
163	deserialfunc = numeric_avg_deserialize,
164	combinefunc = numeric_avg_combine
165);
166
167-- Ensure all these functions made it into the catalog
168SELECT aggfnoid,aggtransfn,aggcombinefn,aggtranstype,aggserialfn,aggdeserialfn
169FROM pg_aggregate
170WHERE aggfnoid = 'myavg'::REGPROC;
171
172DROP AGGREGATE myavg (numeric);
173
174-- invalid: bad parallel-safety marking
175CREATE AGGREGATE mysum (int)
176(
177	stype = int,
178	sfunc = int4pl,
179	parallel = pear
180);
181
182-- invalid: nonstrict inverse with strict forward function
183
184CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS
185$$ SELECT $1 - $2; $$
186LANGUAGE SQL;
187
188CREATE AGGREGATE invalidsumdouble (float8)
189(
190    stype = float8,
191    sfunc = float8pl,
192    mstype = float8,
193    msfunc = float8pl,
194    minvfunc = float8mi_n
195);
196
197-- invalid: non-matching result types
198
199CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS
200$$ SELECT CAST($1 - $2 AS INT); $$
201LANGUAGE SQL;
202
203CREATE AGGREGATE wrongreturntype (float8)
204(
205    stype = float8,
206    sfunc = float8pl,
207    mstype = float8,
208    msfunc = float8pl,
209    minvfunc = float8mi_int
210);
211