src/test/regress/expected/create_aggregate.out

   1 --
   2 -- CREATE_AGGREGATE
   3 --
   4 -- all functions CREATEd
   5 CREATE AGGREGATE newavg (
   6    sfunc = int4_avg_accum, basetype = int4, stype = _int8,
   7    finalfunc = int8_avg,
   8    initcond1 = '{0,0}'
   9 );
  10 -- test comments
  11 COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment';
  12 ERROR:  aggregate newavg_wrong(integer) does not exist
  13 COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment';
  14 COMMENT ON AGGREGATE newavg (int4) IS NULL;
  15 -- without finalfunc; test obsolete spellings 'sfunc1' etc
  16 CREATE AGGREGATE newsum (
  17    sfunc1 = int4pl, basetype = int4, stype1 = int4,
  18    initcond1 = '0'
  19 );
  20 -- zero-argument aggregate
  21 CREATE 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)
  26 CREATE AGGREGATE oldcnt (
  27    sfunc = int8inc, basetype = 'ANY', stype = int8,
  28    initcond = '0'
  29 );
  30 -- aggregate that only cares about null/nonnull input
  31 CREATE AGGREGATE newcnt ("any") (
  32    sfunc = int8inc_any, stype = int8,
  33    initcond = '0'
  34 );
  35 COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail';
  36 ERROR:  aggregate nosuchagg(*) does not exist
  37 COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment';
  38 COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment';
  39 -- multi-argument aggregate
  40 create function sum3(int8,int8,int8) returns int8 as
  41 'select $1 + $2 + $3' language sql strict immutable;
  42 create aggregate sum2(int8,int8) (
  43    sfunc = sum3, stype = int8,
  44    initcond = '0'
  45 );
  46 -- multi-argument aggregates sensitive to distinct/order, strict/nonstrict
  47 create type aggtype as (a integer, b integer, c text);
  48 create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[]
  49 as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
  50 language sql strict immutable;
  51 create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[]
  52 as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
  53 language sql immutable;
  54 create aggregate aggfstr(integer,integer,text) (
  55    sfunc = aggf_trans, stype = aggtype[],
  56    initcond = '{}'
  57 );
  58 create aggregate aggfns(integer,integer,text) (
  59    sfunc = aggfns_trans, stype = aggtype[], sspace = 10000,
  60    initcond = '{}'
  61 );
  62 -- check error cases that would require run-time type coercion
  63 create function least_accum(int8, int8) returns int8 language sql as
  64   'select least($1, $2)';
  65 create aggregate least_agg(int4) (
  66   stype = int8, sfunc = least_accum
  67 );  -- fails
  68 ERROR:  function least_accum(bigint, bigint) requires run-time type coercion
  69 drop function least_accum(int8, int8);
  70 create function least_accum(anycompatible, anycompatible)
  71 returns anycompatible language sql as
  72   'select least($1, $2)';
  73 create aggregate least_agg(int4) (
  74   stype = int8, sfunc = least_accum
  75 );  -- fails
  76 ERROR:  function least_accum(bigint, bigint) requires run-time type coercion
  77 create aggregate least_agg(int8) (
  78   stype = int8, sfunc = least_accum
  79 );
  80 drop function least_accum(anycompatible, anycompatible) cascade;
  81 NOTICE:  drop cascades to function least_agg(bigint)
  82 -- variadic aggregates
  83 create function least_accum(anyelement, variadic anyarray)
  84 returns anyelement language sql as
  85   'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
  86 create aggregate least_agg(variadic items anyarray) (
  87   stype = anyelement, sfunc = least_accum
  88 );
  89 create function cleast_accum(anycompatible, variadic anycompatiblearray)
  90 returns anycompatible language sql as
  91   'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
  92 create aggregate cleast_agg(variadic items anycompatiblearray) (
  93   stype = anycompatible, sfunc = cleast_accum
  94 );
  95 -- test ordered-set aggs using built-in support functions
  96 create aggregate my_percentile_disc(float8 ORDER BY anyelement) (
  97   stype = internal,
  98   sfunc = ordered_set_transition,
  99   finalfunc = percentile_disc_final,
 100   finalfunc_extra = true,
 101   finalfunc_modify = read_write
 102 );
 103 create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") (
 104   stype = internal,
 105   sfunc = ordered_set_transition_multi,
 106   finalfunc = rank_final,
 107   finalfunc_extra = true,
 108   hypothetical
 109 );
 110 alter aggregate my_percentile_disc(float8 ORDER BY anyelement)
 111   rename to test_percentile_disc;
 112 alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any")
 113   rename to test_rank;
 114 \da test_*
 115                                        List of aggregate functions
 116  Schema |         Name         | Result data type |          Argument data types           | Description
 117 --------+----------------------+------------------+----------------------------------------+-------------
 118  public | test_percentile_disc | anyelement       | double precision ORDER BY anyelement   |
 119  public | test_rank            | bigint           | VARIADIC "any" ORDER BY VARIADIC "any" |
 120 (2 rows)
 121
 122 -- moving-aggregate options
 123 CREATE AGGREGATE sumdouble (float8)
 124 (
 125     stype = float8,
 126     sfunc = float8pl,
 127     mstype = float8,
 128     msfunc = float8pl,
 129     minvfunc = float8mi
 130 );
 131 -- aggregate combine and serialization functions
 132 -- can't specify just one of serialfunc and deserialfunc
 133 CREATE AGGREGATE myavg (numeric)
 134 (
 135         stype = internal,
 136         sfunc = numeric_avg_accum,
 137         serialfunc = numeric_avg_serialize
 138 );
 139 ERROR:  must specify both or neither of serialization and deserialization functions
 140 -- serialfunc must have correct parameters
 141 CREATE AGGREGATE myavg (numeric)
 142 (
 143         stype = internal,
 144         sfunc = numeric_avg_accum,
 145         serialfunc = numeric_avg_deserialize,
 146         deserialfunc = numeric_avg_deserialize
 147 );
 148 ERROR:  function numeric_avg_deserialize(internal) does not exist
 149 -- deserialfunc must have correct parameters
 150 CREATE AGGREGATE myavg (numeric)
 151 (
 152         stype = internal,
 153         sfunc = numeric_avg_accum,
 154         serialfunc = numeric_avg_serialize,
 155         deserialfunc = numeric_avg_serialize
 156 );
 157 ERROR:  function numeric_avg_serialize(bytea, internal) does not exist
 158 -- ensure combine function parameters are checked
 159 CREATE AGGREGATE myavg (numeric)
 160 (
 161         stype = internal,
 162         sfunc = numeric_avg_accum,
 163         serialfunc = numeric_avg_serialize,
 164         deserialfunc = numeric_avg_deserialize,
 165         combinefunc = int4larger
 166 );
 167 ERROR:  function int4larger(internal, internal) does not exist
 168 -- ensure create aggregate works.
 169 CREATE AGGREGATE myavg (numeric)
 170 (
 171         stype = internal,
 172         sfunc = numeric_avg_accum,
 173         finalfunc = numeric_avg,
 174         serialfunc = numeric_avg_serialize,
 175         deserialfunc = numeric_avg_deserialize,
 176         combinefunc = numeric_avg_combine,
 177         finalfunc_modify = shareable  -- just to test a non-default setting
 178 );
 179 -- Ensure all these functions made it into the catalog
 180 SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype,
 181        aggserialfn, aggdeserialfn, aggfinalmodify
 182 FROM pg_aggregate
 183 WHERE aggfnoid = 'myavg'::REGPROC;
 184  aggfnoid |    aggtransfn     |    aggcombinefn     | aggtranstype |      aggserialfn      |      aggdeserialfn      | aggfinalmodify
 185 ----------+-------------------+---------------------+--------------+-----------------------+-------------------------+----------------
 186  myavg    | numeric_avg_accum | numeric_avg_combine | internal     | numeric_avg_serialize | numeric_avg_deserialize | s
 187 (1 row)
 188
 189 DROP AGGREGATE myavg (numeric);
 190 -- create or replace aggregate
 191 CREATE AGGREGATE myavg (numeric)
 192 (
 193         stype = internal,
 194         sfunc = numeric_avg_accum,
 195         finalfunc = numeric_avg
 196 );
 197 CREATE OR REPLACE AGGREGATE myavg (numeric)
 198 (
 199         stype = internal,
 200         sfunc = numeric_avg_accum,
 201         finalfunc = numeric_avg,
 202         serialfunc = numeric_avg_serialize,
 203         deserialfunc = numeric_avg_deserialize,
 204         combinefunc = numeric_avg_combine,
 205         finalfunc_modify = shareable  -- just to test a non-default setting
 206 );
 207 -- Ensure all these functions made it into the catalog again
 208 SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype,
 209        aggserialfn, aggdeserialfn, aggfinalmodify
 210 FROM pg_aggregate
 211 WHERE aggfnoid = 'myavg'::REGPROC;
 212  aggfnoid |    aggtransfn     |    aggcombinefn     | aggtranstype |      aggserialfn      |      aggdeserialfn      | aggfinalmodify
 213 ----------+-------------------+---------------------+--------------+-----------------------+-------------------------+----------------
 214  myavg    | numeric_avg_accum | numeric_avg_combine | internal     | numeric_avg_serialize | numeric_avg_deserialize | s
 215 (1 row)
 216
 217 -- can change stype:
 218 CREATE OR REPLACE AGGREGATE myavg (numeric)
 219 (
 220         stype = numeric,
 221         sfunc = numeric_add
 222 );
 223 SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype,
 224        aggserialfn, aggdeserialfn, aggfinalmodify
 225 FROM pg_aggregate
 226 WHERE aggfnoid = 'myavg'::REGPROC;
 227  aggfnoid | aggtransfn  | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify
 228 ----------+-------------+--------------+--------------+-------------+---------------+----------------
 229  myavg    | numeric_add | -            | numeric      | -           | -             | r
 230 (1 row)
 231
 232 -- can't change return type:
 233 CREATE OR REPLACE AGGREGATE myavg (numeric)
 234 (
 235         stype = numeric,
 236         sfunc = numeric_add,
 237         finalfunc = numeric_out
 238 );
 239 ERROR:  cannot change return type of existing function
 240 HINT:  Use DROP AGGREGATE myavg(numeric) first.
 241 -- can't change to a different kind:
 242 CREATE OR REPLACE AGGREGATE myavg (order by numeric)
 243 (
 244         stype = numeric,
 245         sfunc = numeric_add
 246 );
 247 ERROR:  cannot change routine kind
 248 DETAIL:  "myavg" is an ordinary aggregate function.
 249 -- can't change plain function to aggregate:
 250 create function sum4(int8,int8,int8,int8) returns int8 as
 251 'select $1 + $2 + $3 + $4' language sql strict immutable;
 252 CREATE OR REPLACE AGGREGATE sum3 (int8,int8,int8)
 253 (
 254         stype = int8,
 255         sfunc = sum4
 256 );
 257 ERROR:  cannot change routine kind
 258 DETAIL:  "sum3" is a function.
 259 drop function sum4(int8,int8,int8,int8);
 260 DROP AGGREGATE myavg (numeric);
 261 -- invalid: bad parallel-safety marking
 262 CREATE AGGREGATE mysum (int)
 263 (
 264         stype = int,
 265         sfunc = int4pl,
 266         parallel = pear
 267 );
 268 ERROR:  parameter "parallel" must be SAFE, RESTRICTED, or UNSAFE
 269 -- invalid: nonstrict inverse with strict forward function
 270 CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS
 271 $$ SELECT $1 - $2; $$
 272 LANGUAGE SQL;
 273 CREATE AGGREGATE invalidsumdouble (float8)
 274 (
 275     stype = float8,
 276     sfunc = float8pl,
 277     mstype = float8,
 278     msfunc = float8pl,
 279     minvfunc = float8mi_n
 280 );
 281 ERROR:  strictness of aggregate's forward and inverse transition functions must match
 282 -- invalid: non-matching result types
 283 CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS
 284 $$ SELECT CAST($1 - $2 AS INT); $$
 285 LANGUAGE SQL;
 286 CREATE AGGREGATE wrongreturntype (float8)
 287 (
 288     stype = float8,
 289     sfunc = float8pl,
 290     mstype = float8,
 291     msfunc = float8pl,
 292     minvfunc = float8mi_int
 293 );
 294 ERROR:  return type of inverse transition function float8mi_int is not double precision
 295 -- invalid: non-lowercase quoted identifiers
 296 CREATE AGGREGATE case_agg ( -- old syntax
 297         "Sfunc1" = int4pl,
 298         "Basetype" = int4,
 299         "Stype1" = int4,
 300         "Initcond1" = '0',
 301         "Parallel" = safe
 302 );
 303 WARNING:  aggregate attribute "Sfunc1" not recognized
 304 WARNING:  aggregate attribute "Basetype" not recognized
 305 WARNING:  aggregate attribute "Stype1" not recognized
 306 WARNING:  aggregate attribute "Initcond1" not recognized
 307 WARNING:  aggregate attribute "Parallel" not recognized
 308 ERROR:  aggregate stype must be specified
 309 CREATE AGGREGATE case_agg(float8)
 310 (
 311         "Stype" = internal,
 312         "Sfunc" = ordered_set_transition,
 313         "Finalfunc" = percentile_disc_final,
 314         "Finalfunc_extra" = true,
 315         "Finalfunc_modify" = read_write,
 316         "Parallel" = safe
 317 );
 318 WARNING:  aggregate attribute "Stype" not recognized
 319 WARNING:  aggregate attribute "Sfunc" not recognized
 320 WARNING:  aggregate attribute "Finalfunc" not recognized
 321 WARNING:  aggregate attribute "Finalfunc_extra" not recognized
 322 WARNING:  aggregate attribute "Finalfunc_modify" not recognized
 323 WARNING:  aggregate attribute "Parallel" not recognized
 324 ERROR:  aggregate stype must be specified