1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
--
-- CREATE_AGGREGATE
--
-- all functions CREATEd
CREATE AGGREGATE newavg (
sfunc = int4_avg_accum, basetype = int4, stype = _int8,
finalfunc = int8_avg,
initcond1 = '{0,0}'
);
-- test comments
COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment';
ERROR: aggregate newavg_wrong(integer) does not exist
COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment';
COMMENT ON AGGREGATE newavg (int4) IS NULL;
-- without finalfunc; test obsolete spellings 'sfunc1' etc
CREATE AGGREGATE newsum (
sfunc1 = int4pl, basetype = int4, stype1 = int4,
initcond1 = '0'
);
-- zero-argument aggregate
CREATE AGGREGATE newcnt (*) (
sfunc = int8inc, stype = int8,
initcond = '0'
);
-- old-style spelling of same
CREATE AGGREGATE oldcnt (
sfunc = int8inc, basetype = 'ANY', stype = int8,
initcond = '0'
);
-- aggregate that only cares about null/nonnull input
CREATE AGGREGATE newcnt ("any") (
sfunc = int8inc_any, stype = int8,
initcond = '0'
);
COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail';
ERROR: aggregate nosuchagg(*) does not exist
COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment';
COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment';
-- multi-argument aggregate
create function sum3(int8,int8,int8) returns int8 as
'select $1 + $2 + $3' language sql strict immutable;
create aggregate sum2(int8,int8) (
sfunc = sum3, stype = int8,
initcond = '0'
);
-- multi-argument aggregates sensitive to distinct/order, strict/nonstrict
create type aggtype as (a integer, b integer, c text);
create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql strict immutable;
create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql immutable;
create aggregate aggfstr(integer,integer,text) (
sfunc = aggf_trans, stype = aggtype[],
initcond = '{}'
);
create aggregate aggfns(integer,integer,text) (
sfunc = aggfns_trans, stype = aggtype[], sspace = 10000,
initcond = '{}'
);
-- variadic aggregate
create function least_accum(anyelement, variadic anyarray)
returns anyelement language sql as
'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
create aggregate least_agg(variadic items anyarray) (
stype = anyelement, sfunc = least_accum
);
-- test ordered-set aggs using built-in support functions
create aggregate my_percentile_disc(float8 ORDER BY anyelement) (
stype = internal,
sfunc = ordered_set_transition,
finalfunc = percentile_disc_final,
finalfunc_extra = true
);
create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") (
stype = internal,
sfunc = ordered_set_transition_multi,
finalfunc = rank_final,
finalfunc_extra = true,
hypothetical
);
alter aggregate my_percentile_disc(float8 ORDER BY anyelement)
rename to test_percentile_disc;
alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any")
rename to test_rank;
\da test_*
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
--------+----------------------+------------------+----------------------------------------+-------------
public | test_percentile_disc | anyelement | double precision ORDER BY anyelement |
public | test_rank | bigint | VARIADIC "any" ORDER BY VARIADIC "any" |
(2 rows)
-- moving-aggregate options
CREATE AGGREGATE sumdouble (float8)
(
stype = float8,
sfunc = float8pl,
mstype = float8,
msfunc = float8pl,
minvfunc = float8mi
);
-- invalid: nonstrict inverse with strict forward function
CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS
$$ SELECT $1 - $2; $$
LANGUAGE SQL;
CREATE AGGREGATE invalidsumdouble (float8)
(
stype = float8,
sfunc = float8pl,
mstype = float8,
msfunc = float8pl,
minvfunc = float8mi_n
);
ERROR: strictness of aggregate's forward and inverse transition functions must match
-- invalid: non-matching result types
CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS
$$ SELECT CAST($1 - $2 AS INT); $$
LANGUAGE SQL;
CREATE AGGREGATE wrongreturntype (float8)
(
stype = float8,
sfunc = float8pl,
mstype = float8,
msfunc = float8pl,
minvfunc = float8mi_int
);
ERROR: return type of inverse transition function float8mi_int is not double precision
|
