diff options
Diffstat (limited to 'src/test/regress/expected/create_aggregate.out')
| -rw-r--r-- | src/test/regress/expected/create_aggregate.out | 324 |
1 files changed, 324 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out new file mode 100644 index 0000000..dcf6909 --- /dev/null +++ b/src/test/regress/expected/create_aggregate.out @@ -0,0 +1,324 @@ +-- +-- CREATE_AGGREGATE +-- +-- all functions CREATEd +CREATE AGGREGATE newavg ( + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' +); +-- test comments +COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; +ERROR: aggregate newavg_wrong(integer) does not exist +COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment'; +COMMENT ON AGGREGATE newavg (int4) IS NULL; +-- without finalfunc; test obsolete spellings 'sfunc1' etc +CREATE AGGREGATE newsum ( + sfunc1 = int4pl, basetype = int4, stype1 = int4, + initcond1 = '0' +); +-- zero-argument aggregate +CREATE AGGREGATE newcnt (*) ( + sfunc = int8inc, stype = int8, + initcond = '0', parallel = safe +); +-- old-style spelling of same (except without parallel-safe; that's too new) +CREATE AGGREGATE oldcnt ( + sfunc = int8inc, basetype = 'ANY', stype = int8, + initcond = '0' +); +-- aggregate that only cares about null/nonnull input +CREATE AGGREGATE newcnt ("any") ( + sfunc = int8inc_any, stype = int8, + initcond = '0' +); +COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail'; +ERROR: aggregate nosuchagg(*) does not exist +COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment'; +COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment'; +-- multi-argument aggregate +create function sum3(int8,int8,int8) returns int8 as +'select $1 + $2 + $3' language sql strict immutable; +create aggregate sum2(int8,int8) ( + sfunc = sum3, stype = int8, + initcond = '0' +); +-- multi-argument aggregates sensitive to distinct/order, strict/nonstrict +create type aggtype as (a integer, b integer, c text); +create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[] +as 'select array_append($1,ROW($2,$3,$4)::aggtype)' +language sql strict immutable; +create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[] +as 'select array_append($1,ROW($2,$3,$4)::aggtype)' +language sql immutable; +create aggregate aggfstr(integer,integer,text) ( + sfunc = aggf_trans, stype = aggtype[], + initcond = '{}' +); +create aggregate aggfns(integer,integer,text) ( + sfunc = aggfns_trans, stype = aggtype[], sspace = 10000, + initcond = '{}' +); +-- check error cases that would require run-time type coercion +create function least_accum(int8, int8) returns int8 language sql as + 'select least($1, $2)'; +create aggregate least_agg(int4) ( + stype = int8, sfunc = least_accum +); -- fails +ERROR: function least_accum(bigint, bigint) requires run-time type coercion +drop function least_accum(int8, int8); +create function least_accum(anycompatible, anycompatible) +returns anycompatible language sql as + 'select least($1, $2)'; +create aggregate least_agg(int4) ( + stype = int8, sfunc = least_accum +); -- fails +ERROR: function least_accum(bigint, bigint) requires run-time type coercion +create aggregate least_agg(int8) ( + stype = int8, sfunc = least_accum +); +drop function least_accum(anycompatible, anycompatible) cascade; +NOTICE: drop cascades to function least_agg(bigint) +-- variadic aggregates +create function least_accum(anyelement, variadic anyarray) +returns anyelement language sql as + 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; +create aggregate least_agg(variadic items anyarray) ( + stype = anyelement, sfunc = least_accum +); +create function cleast_accum(anycompatible, variadic anycompatiblearray) +returns anycompatible language sql as + 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; +create aggregate cleast_agg(variadic items anycompatiblearray) ( + stype = anycompatible, sfunc = cleast_accum +); +-- test ordered-set aggs using built-in support functions +create aggregate my_percentile_disc(float8 ORDER BY anyelement) ( + stype = internal, + sfunc = ordered_set_transition, + finalfunc = percentile_disc_final, + finalfunc_extra = true, + finalfunc_modify = read_write +); +create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") ( + stype = internal, + sfunc = ordered_set_transition_multi, + finalfunc = rank_final, + finalfunc_extra = true, + hypothetical +); +alter aggregate my_percentile_disc(float8 ORDER BY anyelement) + rename to test_percentile_disc; +alter aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") + rename to test_rank; +\da test_* + List of aggregate functions + Schema | Name | Result data type | Argument data types | Description +--------+----------------------+------------------+----------------------------------------+------------- + public | test_percentile_disc | anyelement | double precision ORDER BY anyelement | + public | test_rank | bigint | VARIADIC "any" ORDER BY VARIADIC "any" | +(2 rows) + +-- moving-aggregate options +CREATE AGGREGATE sumdouble (float8) +( + stype = float8, + sfunc = float8pl, + mstype = float8, + msfunc = float8pl, + minvfunc = float8mi +); +-- aggregate combine and serialization functions +-- can't specify just one of serialfunc and deserialfunc +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + serialfunc = numeric_avg_serialize +); +ERROR: must specify both or neither of serialization and deserialization functions +-- serialfunc must have correct parameters +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + serialfunc = numeric_avg_deserialize, + deserialfunc = numeric_avg_deserialize +); +ERROR: function numeric_avg_deserialize(internal) does not exist +-- deserialfunc must have correct parameters +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + serialfunc = numeric_avg_serialize, + deserialfunc = numeric_avg_serialize +); +ERROR: function numeric_avg_serialize(bytea, internal) does not exist +-- ensure combine function parameters are checked +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + serialfunc = numeric_avg_serialize, + deserialfunc = numeric_avg_deserialize, + combinefunc = int4larger +); +ERROR: function int4larger(internal, internal) does not exist +-- ensure create aggregate works. +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + finalfunc = numeric_avg, + serialfunc = numeric_avg_serialize, + deserialfunc = numeric_avg_deserialize, + combinefunc = numeric_avg_combine, + finalfunc_modify = shareable -- just to test a non-default setting +); +-- Ensure all these functions made it into the catalog +SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, + aggserialfn, aggdeserialfn, aggfinalmodify +FROM pg_aggregate +WHERE aggfnoid = 'myavg'::REGPROC; + aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify +----------+-------------------+---------------------+--------------+-----------------------+-------------------------+---------------- + myavg | numeric_avg_accum | numeric_avg_combine | internal | numeric_avg_serialize | numeric_avg_deserialize | s +(1 row) + +DROP AGGREGATE myavg (numeric); +-- create or replace aggregate +CREATE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + finalfunc = numeric_avg +); +CREATE OR REPLACE AGGREGATE myavg (numeric) +( + stype = internal, + sfunc = numeric_avg_accum, + finalfunc = numeric_avg, + serialfunc = numeric_avg_serialize, + deserialfunc = numeric_avg_deserialize, + combinefunc = numeric_avg_combine, + finalfunc_modify = shareable -- just to test a non-default setting +); +-- Ensure all these functions made it into the catalog again +SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, + aggserialfn, aggdeserialfn, aggfinalmodify +FROM pg_aggregate +WHERE aggfnoid = 'myavg'::REGPROC; + aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify +----------+-------------------+---------------------+--------------+-----------------------+-------------------------+---------------- + myavg | numeric_avg_accum | numeric_avg_combine | internal | numeric_avg_serialize | numeric_avg_deserialize | s +(1 row) + +-- can change stype: +CREATE OR REPLACE AGGREGATE myavg (numeric) +( + stype = numeric, + sfunc = numeric_add +); +SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype, + aggserialfn, aggdeserialfn, aggfinalmodify +FROM pg_aggregate +WHERE aggfnoid = 'myavg'::REGPROC; + aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify +----------+-------------+--------------+--------------+-------------+---------------+---------------- + myavg | numeric_add | - | numeric | - | - | r +(1 row) + +-- can't change return type: +CREATE OR REPLACE AGGREGATE myavg (numeric) +( + stype = numeric, + sfunc = numeric_add, + finalfunc = numeric_out +); +ERROR: cannot change return type of existing function +HINT: Use DROP AGGREGATE myavg(numeric) first. +-- can't change to a different kind: +CREATE OR REPLACE AGGREGATE myavg (order by numeric) +( + stype = numeric, + sfunc = numeric_add +); +ERROR: cannot change routine kind +DETAIL: "myavg" is an ordinary aggregate function. +-- can't change plain function to aggregate: +create function sum4(int8,int8,int8,int8) returns int8 as +'select $1 + $2 + $3 + $4' language sql strict immutable; +CREATE OR REPLACE AGGREGATE sum3 (int8,int8,int8) +( + stype = int8, + sfunc = sum4 +); +ERROR: cannot change routine kind +DETAIL: "sum3" is a function. +drop function sum4(int8,int8,int8,int8); +DROP AGGREGATE myavg (numeric); +-- invalid: bad parallel-safety marking +CREATE AGGREGATE mysum (int) +( + stype = int, + sfunc = int4pl, + parallel = pear +); +ERROR: parameter "parallel" must be SAFE, RESTRICTED, or UNSAFE +-- invalid: nonstrict inverse with strict forward function +CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS +$$ SELECT $1 - $2; $$ +LANGUAGE SQL; +CREATE AGGREGATE invalidsumdouble (float8) +( + stype = float8, + sfunc = float8pl, + mstype = float8, + msfunc = float8pl, + minvfunc = float8mi_n +); +ERROR: strictness of aggregate's forward and inverse transition functions must match +-- invalid: non-matching result types +CREATE FUNCTION float8mi_int(float8, float8) RETURNS int AS +$$ SELECT CAST($1 - $2 AS INT); $$ +LANGUAGE SQL; +CREATE AGGREGATE wrongreturntype (float8) +( + stype = float8, + sfunc = float8pl, + mstype = float8, + msfunc = float8pl, + minvfunc = float8mi_int +); +ERROR: return type of inverse transition function float8mi_int is not double precision +-- invalid: non-lowercase quoted identifiers +CREATE AGGREGATE case_agg ( -- old syntax + "Sfunc1" = int4pl, + "Basetype" = int4, + "Stype1" = int4, + "Initcond1" = '0', + "Parallel" = safe +); +WARNING: aggregate attribute "Sfunc1" not recognized +WARNING: aggregate attribute "Basetype" not recognized +WARNING: aggregate attribute "Stype1" not recognized +WARNING: aggregate attribute "Initcond1" not recognized +WARNING: aggregate attribute "Parallel" not recognized +ERROR: aggregate stype must be specified +CREATE AGGREGATE case_agg(float8) +( + "Stype" = internal, + "Sfunc" = ordered_set_transition, + "Finalfunc" = percentile_disc_final, + "Finalfunc_extra" = true, + "Finalfunc_modify" = read_write, + "Parallel" = safe +); +WARNING: aggregate attribute "Stype" not recognized +WARNING: aggregate attribute "Sfunc" not recognized +WARNING: aggregate attribute "Finalfunc" not recognized +WARNING: aggregate attribute "Finalfunc_extra" not recognized +WARNING: aggregate attribute "Finalfunc_modify" not recognized +WARNING: aggregate attribute "Parallel" not recognized +ERROR: aggregate stype must be specified |