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