src/test/regress/sql/create_aggregate.sql - cloudberry - Git at Google

 --
 -- 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';
 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';
 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 CONTAINS 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

 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

 create aggregate least_agg(int8) (
   stype = int8, sfunc = least_accum
 );

 drop function least_accum(anycompatible, anycompatible) cascade;

 -- 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_*

 -- 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
 );

 -- serialfunc must have correct parameters
 CREATE AGGREGATE myavg (numeric)
 (
 	stype = internal,
 	sfunc = numeric_avg_accum,
 	serialfunc = numeric_avg_deserialize,
 	deserialfunc = numeric_avg_deserialize
 );

 -- deserialfunc must have correct parameters
 CREATE AGGREGATE myavg (numeric)
 (
 	stype = internal,
 	sfunc = numeric_avg_accum,
 	serialfunc = numeric_avg_serialize,
 	deserialfunc = numeric_avg_serialize
 );

 -- 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
 );

 -- 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;

 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;

 -- 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;

 -- can't change return type:
 CREATE OR REPLACE AGGREGATE myavg (numeric)
 (
 	stype = numeric,
 	sfunc = numeric_add,
 	finalfunc = numeric_out
 );

 -- can't change to a different kind:
 CREATE OR REPLACE AGGREGATE myavg (order by numeric)
 (
 	stype = numeric,
 	sfunc = numeric_add
 );

 -- 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
 );

 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
 );

 -- 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
 );

 -- 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
 );

 -- invalid: non-lowercase quoted identifiers

 CREATE AGGREGATE case_agg ( -- old syntax
 	"Sfunc1" = int4pl,
 	"Basetype" = int4,
 	"Stype1" = int4,
 	"Initcond1" = '0',
 	"Parallel" = safe
 );

 CREATE AGGREGATE case_agg(float8)
 (
 	"Stype" = internal,
 	"Sfunc" = ordered_set_transition,
 	"Finalfunc" = percentile_disc_final,
 	"Finalfunc_extra" = true,
 	"Finalfunc_modify" = read_write,
 	"Parallel" = safe
 );


 -- Negative test: "ordered aggregate prefunc is not supported"
 create ordered aggregate should_error(integer,integer,text) (
    stype = aggtype[],
    sfunc = aggfns_trans,
    combinefunc = array_cat,
    initcond = '{}'
 );

 -- MPP-2863: ensure that aggregate declarations with an initial value == ''
 -- do not get converted to an initial value == NULL
 create function str_concat(t1 text, t2 text) returns text as
 $$
     select $1 || $2;
 $$ language sql CONTAINS SQL;

 CREATE AGGREGATE string_concat (sfunc = str_concat, combinefunc=str_concat, basetype = 'text', stype = text,initcond = '');

 create table aggtest2(i int, t text) DISTRIBUTED BY (i);
 insert into aggtest2 values(1, 'hello');
 insert into aggtest2 values(2, 'hello');
 select string_concat(t) from aggtest2;
 select string_concat(t) from (select * from aggtest2 limit 2000) tmp;
 drop table aggtest2;
 drop aggregate string_concat(text);
 drop function str_concat(text, text);
	--
	-- 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';
	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';
	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 CONTAINS 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

	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

	create aggregate least_agg(int8) (
	stype = int8, sfunc = least_accum
	);

	drop function least_accum(anycompatible, anycompatible) cascade;

	-- 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_*

	-- 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
	);

	-- serialfunc must have correct parameters
	CREATE AGGREGATE myavg (numeric)
	(
	stype = internal,
	sfunc = numeric_avg_accum,
	serialfunc = numeric_avg_deserialize,
	deserialfunc = numeric_avg_deserialize
	);

	-- deserialfunc must have correct parameters
	CREATE AGGREGATE myavg (numeric)
	(
	stype = internal,
	sfunc = numeric_avg_accum,
	serialfunc = numeric_avg_serialize,
	deserialfunc = numeric_avg_serialize
	);

	-- 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
	);

	-- 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;

	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;

	-- 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;

	-- can't change return type:
	CREATE OR REPLACE AGGREGATE myavg (numeric)
	(
	stype = numeric,
	sfunc = numeric_add,
	finalfunc = numeric_out
	);

	-- can't change to a different kind:
	CREATE OR REPLACE AGGREGATE myavg (order by numeric)
	(
	stype = numeric,
	sfunc = numeric_add
	);

	-- 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
	);

	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
	);

	-- 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
	);

	-- 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
	);

	-- invalid: non-lowercase quoted identifiers

	CREATE AGGREGATE case_agg ( -- old syntax
	"Sfunc1" = int4pl,
	"Basetype" = int4,
	"Stype1" = int4,
	"Initcond1" = '0',
	"Parallel" = safe
	);

	CREATE AGGREGATE case_agg(float8)
	(
	"Stype" = internal,
	"Sfunc" = ordered_set_transition,
	"Finalfunc" = percentile_disc_final,
	"Finalfunc_extra" = true,
	"Finalfunc_modify" = read_write,
	"Parallel" = safe
	);


	-- Negative test: "ordered aggregate prefunc is not supported"
	create ordered aggregate should_error(integer,integer,text) (
	stype = aggtype[],
	sfunc = aggfns_trans,
	combinefunc = array_cat,
	initcond = '{}'
	);

	-- MPP-2863: ensure that aggregate declarations with an initial value == ''
	-- do not get converted to an initial value == NULL
	create function str_concat(t1 text, t2 text) returns text as
	$$
	select $1 \|\| $2;
	$$ language sql CONTAINS SQL;

	CREATE AGGREGATE string_concat (sfunc = str_concat, combinefunc=str_concat, basetype = 'text', stype = text,initcond = '');

	create table aggtest2(i int, t text) DISTRIBUTED BY (i);
	insert into aggtest2 values(1, 'hello');
	insert into aggtest2 values(2, 'hello');
	select string_concat(t) from aggtest2;
	select string_concat(t) from (select * from aggtest2 limit 2000) tmp;
	drop table aggtest2;
	drop aggregate string_concat(text);
	drop function str_concat(text, text);