diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/create_aggregate.out | 71 | ||||
| -rw-r--r-- | src/test/regress/sql/create_aggregate.sql | 65 |
2 files changed, 136 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out index 3d92084e130..a2eb9996e15 100644 --- a/src/test/regress/expected/create_aggregate.out +++ b/src/test/regress/expected/create_aggregate.out @@ -161,6 +161,77 @@ WHERE aggfnoid = 'myavg'::REGPROC; (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) ( diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql index cb6552e2d68..fd7cd400c19 100644 --- a/src/test/regress/sql/create_aggregate.sql +++ b/src/test/regress/sql/create_aggregate.sql @@ -174,6 +174,71 @@ 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) ( |