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