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);
76create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") (
77  stype = internal,
78  sfunc = ordered_set_transition_multi,
79  finalfunc = rank_final,
80  finalfunc_extra = true,
81  hypothetical
82);
83alter aggregate my_percentile_disc(float8 ORDER BY anyelement)
84  rename to test_percentile_disc;
85alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any")
86  rename to test_rank;
87\da test_*
88                                       List of aggregate functions
89 Schema |         Name         | Result data type |          Argument data types           | Description
90--------+----------------------+------------------+----------------------------------------+-------------
91 public | test_percentile_disc | anyelement       | double precision ORDER BY anyelement   |
92 public | test_rank            | bigint           | VARIADIC "any" ORDER BY VARIADIC "any" |
93(2 rows)
94
95-- moving-aggregate options
96CREATE AGGREGATE sumdouble (float8)
97(
98    stype = float8,
99    sfunc = float8pl,
100    mstype = float8,
101    msfunc = float8pl,
102    minvfunc = float8mi
103);
104-- aggregate combine and serialization functions
105-- can't specify just one of serialfunc and deserialfunc
106CREATE AGGREGATE myavg (numeric)
107(
108	stype = internal,
109	sfunc = numeric_avg_accum,
110	serialfunc = numeric_avg_serialize
111);
112ERROR:  must specify both or neither of serialization and deserialization functions
113-- serialfunc must have correct parameters
114CREATE AGGREGATE myavg (numeric)
115(
116	stype = internal,
117	sfunc = numeric_avg_accum,
118	serialfunc = numeric_avg_deserialize,
119	deserialfunc = numeric_avg_deserialize
120);
121ERROR:  function numeric_avg_deserialize(internal) does not exist
122-- deserialfunc must have correct parameters
123CREATE AGGREGATE myavg (numeric)
124(
125	stype = internal,
126	sfunc = numeric_avg_accum,
127	serialfunc = numeric_avg_serialize,
128	deserialfunc = numeric_avg_serialize
129);
130ERROR:  function numeric_avg_serialize(bytea, internal) does not exist
131-- ensure combine function parameters are checked
132CREATE AGGREGATE myavg (numeric)
133(
134	stype = internal,
135	sfunc = numeric_avg_accum,
136	serialfunc = numeric_avg_serialize,
137	deserialfunc = numeric_avg_deserialize,
138	combinefunc = int4larger
139);
140ERROR:  function int4larger(internal, internal) does not exist
141-- ensure create aggregate works.
142CREATE AGGREGATE myavg (numeric)
143(
144	stype = internal,
145	sfunc = numeric_avg_accum,
146	finalfunc = numeric_avg,
147	serialfunc = numeric_avg_serialize,
148	deserialfunc = numeric_avg_deserialize,
149	combinefunc = numeric_avg_combine
150);
151-- Ensure all these functions made it into the catalog
152SELECT aggfnoid,aggtransfn,aggcombinefn,aggtranstype,aggserialfn,aggdeserialfn
153FROM pg_aggregate
154WHERE aggfnoid = 'myavg'::REGPROC;
155 aggfnoid |    aggtransfn     |    aggcombinefn     | aggtranstype |      aggserialfn      |      aggdeserialfn
156----------+-------------------+---------------------+--------------+-----------------------+-------------------------
157 myavg    | numeric_avg_accum | numeric_avg_combine |         2281 | numeric_avg_serialize | numeric_avg_deserialize
158(1 row)
159
160DROP AGGREGATE myavg (numeric);
161-- invalid: bad parallel-safety marking
162CREATE AGGREGATE mysum (int)
163(
164	stype = int,
165	sfunc = int4pl,
166	parallel = pear
167);
168ERROR:  parameter "parallel" must be SAFE, RESTRICTED, or UNSAFE
169-- invalid: nonstrict inverse with strict forward function
170CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS
171$$ SELECT $1 - $2; $$
172LANGUAGE SQL;
173CREATE AGGREGATE invalidsumdouble (float8)
174(
175    stype = float8,
176    sfunc = float8pl,
177    mstype = float8,
178    msfunc = float8pl,
179    minvfunc = float8mi_n
180);
181ERROR:  strictness of aggregate's forward and inverse transition functions must match
182-- invalid: non-matching result types
183CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS
184$$ SELECT CAST($1 - $2 AS INT); $$
185LANGUAGE SQL;
186CREATE AGGREGATE wrongreturntype (float8)
187(
188    stype = float8,
189    sfunc = float8pl,
190    mstype = float8,
191    msfunc = float8pl,
192    minvfunc = float8mi_int
193);
194ERROR:  return type of inverse transition function float8mi_int is not double precision
195