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 finalfunc_modify = read_write 91); 92 93create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") ( 94 stype = internal, 95 sfunc = ordered_set_transition_multi, 96 finalfunc = rank_final, 97 finalfunc_extra = true, 98 hypothetical 99); 100 101alter aggregate my_percentile_disc(float8 ORDER BY anyelement) 102 rename to test_percentile_disc; 103alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") 104 rename to test_rank; 105 106\da test_* 107 108-- moving-aggregate options 109 110CREATE AGGREGATE sumdouble (float8) 111( 112 stype = float8, 113 sfunc = float8pl, 114 mstype = float8, 115 msfunc = float8pl, 116 minvfunc = float8mi 117); 118 119-- aggregate combine and serialization functions 120 121-- can't specify just one of serialfunc and deserialfunc 122CREATE AGGREGATE myavg (numeric) 123( 124 stype = internal, 125 sfunc = numeric_avg_accum, 126 serialfunc = numeric_avg_serialize 127); 128 129-- serialfunc must have correct parameters 130CREATE AGGREGATE myavg (numeric) 131( 132 stype = internal, 133 sfunc = numeric_avg_accum, 134 serialfunc = numeric_avg_deserialize, 135 deserialfunc = numeric_avg_deserialize 136); 137 138-- deserialfunc must have correct parameters 139CREATE AGGREGATE myavg (numeric) 140( 141 stype = internal, 142 sfunc = numeric_avg_accum, 143 serialfunc = numeric_avg_serialize, 144 deserialfunc = numeric_avg_serialize 145); 146 147-- ensure combine function parameters are checked 148CREATE AGGREGATE myavg (numeric) 149( 150 stype = internal, 151 sfunc = numeric_avg_accum, 152 serialfunc = numeric_avg_serialize, 153 deserialfunc = numeric_avg_deserialize, 154 combinefunc = int4larger 155); 156 157-- ensure create aggregate works. 158CREATE AGGREGATE myavg (numeric) 159( 160 stype = internal, 161 sfunc = numeric_avg_accum, 162 finalfunc = numeric_avg, 163 serialfunc = numeric_avg_serialize, 164 deserialfunc = numeric_avg_deserialize, 165 combinefunc = numeric_avg_combine, 166 finalfunc_modify = shareable -- just to test a non-default setting 167); 168 169-- Ensure all these functions made it into the catalog 170SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, 171 aggserialfn, aggdeserialfn, aggfinalmodify 172FROM pg_aggregate 173WHERE aggfnoid = 'myavg'::REGPROC; 174 175DROP AGGREGATE myavg (numeric); 176 177-- create or replace aggregate 178CREATE AGGREGATE myavg (numeric) 179( 180 stype = internal, 181 sfunc = numeric_avg_accum, 182 finalfunc = numeric_avg 183); 184 185CREATE OR REPLACE AGGREGATE myavg (numeric) 186( 187 stype = internal, 188 sfunc = numeric_avg_accum, 189 finalfunc = numeric_avg, 190 serialfunc = numeric_avg_serialize, 191 deserialfunc = numeric_avg_deserialize, 192 combinefunc = numeric_avg_combine, 193 finalfunc_modify = shareable -- just to test a non-default setting 194); 195 196-- Ensure all these functions made it into the catalog again 197SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, 198 aggserialfn, aggdeserialfn, aggfinalmodify 199FROM pg_aggregate 200WHERE aggfnoid = 'myavg'::REGPROC; 201 202-- can change stype: 203CREATE OR REPLACE AGGREGATE myavg (numeric) 204( 205 stype = numeric, 206 sfunc = numeric_add 207); 208SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, 209 aggserialfn, aggdeserialfn, aggfinalmodify 210FROM pg_aggregate 211WHERE aggfnoid = 'myavg'::REGPROC; 212 213-- can't change return type: 214CREATE OR REPLACE AGGREGATE myavg (numeric) 215( 216 stype = numeric, 217 sfunc = numeric_add, 218 finalfunc = numeric_out 219); 220 221-- can't change to a different kind: 222CREATE OR REPLACE AGGREGATE myavg (order by numeric) 223( 224 stype = numeric, 225 sfunc = numeric_add 226); 227 228-- can't change plain function to aggregate: 229create function sum4(int8,int8,int8,int8) returns int8 as 230'select $1 + $2 + $3 + $4' language sql strict immutable; 231 232CREATE OR REPLACE AGGREGATE sum3 (int8,int8,int8) 233( 234 stype = int8, 235 sfunc = sum4 236); 237 238drop function sum4(int8,int8,int8,int8); 239 240DROP AGGREGATE myavg (numeric); 241 242-- invalid: bad parallel-safety marking 243CREATE AGGREGATE mysum (int) 244( 245 stype = int, 246 sfunc = int4pl, 247 parallel = pear 248); 249 250-- invalid: nonstrict inverse with strict forward function 251 252CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS 253$$ SELECT $1 - $2; $$ 254LANGUAGE SQL; 255 256CREATE AGGREGATE invalidsumdouble (float8) 257( 258 stype = float8, 259 sfunc = float8pl, 260 mstype = float8, 261 msfunc = float8pl, 262 minvfunc = float8mi_n 263); 264 265-- invalid: non-matching result types 266 267CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS 268$$ SELECT CAST($1 - $2 AS INT); $$ 269LANGUAGE SQL; 270 271CREATE AGGREGATE wrongreturntype (float8) 272( 273 stype = float8, 274 sfunc = float8pl, 275 mstype = float8, 276 msfunc = float8pl, 277 minvfunc = float8mi_int 278); 279 280-- invalid: non-lowercase quoted identifiers 281 282CREATE AGGREGATE case_agg ( -- old syntax 283 "Sfunc1" = int4pl, 284 "Basetype" = int4, 285 "Stype1" = int4, 286 "Initcond1" = '0', 287 "Parallel" = safe 288); 289 290CREATE AGGREGATE case_agg(float8) 291( 292 "Stype" = internal, 293 "Sfunc" = ordered_set_transition, 294 "Finalfunc" = percentile_disc_final, 295 "Finalfunc_extra" = true, 296 "Finalfunc_modify" = read_write, 297 "Parallel" = safe 298); 299