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