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