diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 256 | ||||
| -rw-r--r-- | src/test/regress/expected/create_aggregate.out | 24 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 47 | ||||
| -rw-r--r-- | src/test/regress/sql/aggregates.sql | 95 | ||||
| -rw-r--r-- | src/test/regress/sql/create_aggregate.sql | 21 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 47 |
6 files changed, 462 insertions, 28 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 1a0ca5c5f3c..58df85470a6 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1311,6 +1311,262 @@ select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1) {"(2,2,bar)","(3,1,baz)"} (1 row) +-- ordered-set aggregates +select p, percentile_cont(p) within group (order by x::float8) +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; + p | percentile_cont +------+----------------- + 0 | 1 + 0.1 | 1.4 + 0.25 | 2 + 0.4 | 2.6 + 0.5 | 3 + 0.6 | 3.4 + 0.75 | 4 + 0.9 | 4.6 + 1 | 5 +(9 rows) + +select p, percentile_cont(p order by p) within group (order by x) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; +ERROR: cannot use multiple ORDER BY clauses with WITHIN GROUP +LINE 1: select p, percentile_cont(p order by p) within group (order ... + ^ +select p, sum() within group (order by x::float8) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; +ERROR: sum is not an ordered-set aggregate, so it cannot have WITHIN GROUP +LINE 1: select p, sum() within group (order by x::float8) + ^ +select p, percentile_cont(p,p) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; +ERROR: WITHIN GROUP is required for ordered-set aggregate percentile_cont +LINE 1: select p, percentile_cont(p,p) + ^ +select percentile_cont(0.5) within group (order by b) from aggtest; + percentile_cont +------------------ + 53.4485001564026 +(1 row) + +select percentile_cont(0.5) within group (order by b), sum(b) from aggtest; + percentile_cont | sum +------------------+--------- + 53.4485001564026 | 431.773 +(1 row) + +select percentile_cont(0.5) within group (order by thousand) from tenk1; + percentile_cont +----------------- + 499.5 +(1 row) + +select percentile_disc(0.5) within group (order by thousand) from tenk1; + percentile_disc +----------------- + 499 +(1 row) + +select rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); + rank +------ + 5 +(1 row) + +select cume_dist(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); + cume_dist +----------- + 0.875 +(1 row) + +select percent_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4),(5)) v(x); + percent_rank +-------------- + 0.5 +(1 row) + +select dense_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); + dense_rank +------------ + 3 +(1 row) + +select percentile_disc(array[0,0.1,0.25,0.5,0.75,0.9,1]) within group (order by thousand) +from tenk1; + percentile_disc +---------------------------- + {0,99,249,499,749,899,999} +(1 row) + +select percentile_cont(array[0,0.25,0.5,0.75,1]) within group (order by thousand) +from tenk1; + percentile_cont +----------------------------- + {0,249.75,499.5,749.25,999} +(1 row) + +select percentile_disc(array[[null,1,0.5],[0.75,0.25,null]]) within group (order by thousand) +from tenk1; + percentile_disc +--------------------------------- + {{NULL,999,499},{749,249,NULL}} +(1 row) + +select percentile_cont(array[0,1,0.25,0.75,0.5,1]) within group (order by x) +from generate_series(1,6) x; + percentile_cont +----------------------- + {1,6,2.25,4.75,3.5,6} +(1 row) + +select ten, mode() within group (order by string4) from tenk1 group by ten; + ten | mode +-----+-------- + 0 | HHHHxx + 1 | OOOOxx + 2 | VVVVxx + 3 | OOOOxx + 4 | HHHHxx + 5 | HHHHxx + 6 | OOOOxx + 7 | AAAAxx + 8 | VVVVxx + 9 | VVVVxx +(10 rows) + +select percentile_disc(array[0.25,0.5,0.75]) within group (order by x) +from unnest('{fred,jim,fred,jack,jill,fred,jill,jim,jim,sheila,jim,sheila}'::text[]) u(x); + percentile_disc +----------------- + {fred,jill,jim} +(1 row) + +-- check collation propagates up in suitable cases: +select pg_collation_for(percentile_disc(1) within group (order by x collate "POSIX")) + from (values ('fred'),('jim')) v(x); + pg_collation_for +------------------ + "POSIX" +(1 row) + +-- ordered-set aggs created with CREATE AGGREGATE +select test_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); + test_rank +----------- + 5 +(1 row) + +select test_percentile_disc(0.5) within group (order by thousand) from tenk1; + test_percentile_disc +---------------------- + 499 +(1 row) + +-- ordered-set aggs can't use ungrouped vars in direct args: +select rank(x) within group (order by x) from generate_series(1,5) x; +ERROR: column "x.x" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: select rank(x) within group (order by x) from generate_serie... + ^ +DETAIL: Direct arguments of an ordered-set aggregate must use only grouped columns. +-- outer-level agg can't use a grouped arg of a lower level, either: +select array(select percentile_disc(a) within group (order by x) + from (values (0.3),(0.7)) v(a) group by a) + from generate_series(1,5) g(x); +ERROR: outer-level aggregate cannot contain a lower-level variable in its direct arguments +LINE 1: select array(select percentile_disc(a) within group (order b... + ^ +-- agg in the direct args is a grouping violation, too: +select rank(sum(x)) within group (order by x) from generate_series(1,5) x; +ERROR: aggregate function calls cannot be nested +LINE 1: select rank(sum(x)) within group (order by x) from generate_... + ^ +-- hypothetical-set type unification and argument-count failures: +select rank(3) within group (order by x) from (values ('fred'),('jim')) v(x); +ERROR: WITHIN GROUP types text and integer cannot be matched +LINE 1: select rank(3) within group (order by x) from (values ('fred... + ^ +select rank(3) within group (order by stringu1,stringu2) from tenk1; +ERROR: function rank(integer, name, name) does not exist +LINE 1: select rank(3) within group (order by stringu1,stringu2) fro... + ^ +HINT: To use the hypothetical-set aggregate rank, the number of hypothetical direct arguments (here 1) must match the number of ordering columns (here 2). +select rank('fred') within group (order by x) from generate_series(1,5) x; +ERROR: invalid input syntax for integer: "fred" +LINE 1: select rank('fred') within group (order by x) from generate_... + ^ +select rank('adam'::text collate "C") within group (order by x collate "POSIX") + from (values ('fred'),('jim')) v(x); +ERROR: collation mismatch between explicit collations "C" and "POSIX" +LINE 1: ...adam'::text collate "C") within group (order by x collate "P... + ^ +-- hypothetical-set type unification successes: +select rank('adam'::varchar) within group (order by x) from (values ('fred'),('jim')) v(x); + rank +------ + 1 +(1 row) + +select rank('3') within group (order by x) from generate_series(1,5) x; + rank +------ + 3 +(1 row) + +-- divide by zero check +select percent_rank(0) within group (order by x) from generate_series(1,0) x; + percent_rank +-------------- + 0 +(1 row) + +-- deparse and multiple features: +create view aggordview1 as +select ten, + percentile_disc(0.5) within group (order by thousand) as p50, + percentile_disc(0.5) within group (order by thousand) filter (where hundred=1) as px, + rank(5,'AZZZZ',50) within group (order by hundred, string4 desc, hundred) + from tenk1 + group by ten order by ten; +select pg_get_viewdef('aggordview1'); + pg_get_viewdef +------------------------------------------------------------------------------------------------------------------------------- + SELECT tenk1.ten, + + percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) AS p50, + + percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+ + rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred, tenk1.string4 DESC, tenk1.hundred) AS rank + + FROM tenk1 + + GROUP BY tenk1.ten + + ORDER BY tenk1.ten; +(1 row) + +select * from aggordview1 order by ten; + ten | p50 | px | rank +-----+-----+-----+------ + 0 | 490 | | 101 + 1 | 491 | 401 | 101 + 2 | 492 | | 101 + 3 | 493 | | 101 + 4 | 494 | | 101 + 5 | 495 | | 67 + 6 | 496 | | 1 + 7 | 497 | | 1 + 8 | 498 | | 1 + 9 | 499 | | 1 +(10 rows) + +drop view aggordview1; -- variadic aggregates select least_agg(q1,q2) from int8_tbl; least_agg diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out index 9ecaea14992..ca908d91f45 100644 --- a/src/test/regress/expected/create_aggregate.out +++ b/src/test/regress/expected/create_aggregate.out @@ -66,3 +66,27 @@ returns anyelement language sql as create aggregate least_agg(variadic items anyarray) ( stype = anyelement, sfunc = least_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 +); +create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") ( + stype = internal, + sfunc = ordered_set_transition_multi, + finalfunc = rank_final, + 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) + diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 10d65c0b287..292b6051432 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -23,6 +23,7 @@ SELECT ($1 = $2) OR EXISTS(select 1 from pg_catalog.pg_cast where castsource = $1 and casttarget = $2 and castmethod = 'b' and castcontext = 'i') OR + ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND EXISTS(select 1 from pg_catalog.pg_type where oid = $1 and typelem != 0 and typlen = -1)) @@ -34,6 +35,7 @@ SELECT ($1 = $2) OR EXISTS(select 1 from pg_catalog.pg_cast where castsource = $1 and casttarget = $2 and castmethod = 'b') OR + ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND EXISTS(select 1 from pg_catalog.pg_type where oid = $1 and typelem != 0 and typlen = -1)) @@ -702,7 +704,11 @@ SELECT * FROM funcdescs -- Look for illegal values in pg_aggregate fields. SELECT ctid, aggfnoid::oid FROM pg_aggregate as p1 -WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0 OR aggtransspace < 0; +WHERE aggfnoid = 0 OR aggtransfn = 0 OR + aggkind NOT IN ('n', 'o', 'h') OR + aggnumdirectargs < 0 OR + (aggkind = 'n' AND aggnumdirectargs > 0) OR + aggtranstype = 0 OR aggtransspace < 0; ctid | aggfnoid ------+---------- (0 rows) @@ -711,7 +717,7 @@ WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0 OR aggtransspace < 0; SELECT a.aggfnoid::oid, p.proname FROM pg_aggregate as a, pg_proc as p WHERE a.aggfnoid = p.oid AND - (NOT p.proisagg OR p.proretset); + (NOT p.proisagg OR p.proretset OR p.pronargs < a.aggnumdirectargs); aggfnoid | proname ----------+--------- (0 rows) @@ -742,7 +748,9 @@ FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND a.aggtransfn = ptr.oid AND (ptr.proretset - OR NOT (ptr.pronargs = p.pronargs + 1) + OR NOT (ptr.pronargs = + CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 + ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) OR (p.pronargs > 0 AND @@ -751,7 +759,7 @@ WHERE a.aggfnoid = p.oid AND NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) OR (p.pronargs > 2 AND NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) - -- we could carry the check further, but that's enough for now + -- we could carry the check further, but 3 args is enough for now ); aggfnoid | proname | oid | proname ----------+---------+-----+--------- @@ -762,10 +770,19 @@ SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND a.aggfinalfn = pfn.oid AND - (pfn.proretset - OR NOT binary_coercible(pfn.prorettype, p.prorettype) - OR pfn.pronargs != 1 - OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); + (pfn.proretset OR + NOT binary_coercible(pfn.prorettype, p.prorettype) OR + NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR + CASE WHEN a.aggkind = 'n' THEN pfn.pronargs != 1 + ELSE pfn.pronargs != p.pronargs + 1 + OR (p.pronargs > 0 AND + NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) + OR (p.pronargs > 1 AND + NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) + OR (p.pronargs > 2 AND + NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) + -- we could carry the check further, but 3 args is enough for now + END); aggfnoid | proname | oid | proname ----------+---------+-----+--------- (0 rows) @@ -857,18 +874,20 @@ ORDER BY 1; count("any") | count() (1 row) --- For the same reason, we avoid creating built-in variadic aggregates. +-- For the same reason, built-in aggregates with default arguments are no good. SELECT oid, proname FROM pg_proc AS p -WHERE proisagg AND provariadic != 0; +WHERE proisagg AND proargdefaults IS NOT NULL; oid | proname -----+--------- (0 rows) --- For the same reason, built-in aggregates with default arguments are no good. -SELECT oid, proname -FROM pg_proc AS p -WHERE proisagg AND proargdefaults IS NOT NULL; +-- For the same reason, we avoid creating built-in variadic aggregates, except +-- that variadic ordered-set aggregates are OK (since they have special syntax +-- that is not subject to the misplaced ORDER BY issue). +SELECT p.oid, proname +FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid +WHERE proisagg AND provariadic != 0 AND a.aggkind = 'n'; oid | proname -----+--------- (0 rows) diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index f99a07d9833..8096a6ffbec 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -492,6 +492,101 @@ select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), generate_series(1,2) i; +-- ordered-set aggregates + +select p, percentile_cont(p) within group (order by x::float8) +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; + +select p, percentile_cont(p order by p) within group (order by x) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; + +select p, sum() within group (order by x::float8) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; + +select p, percentile_cont(p,p) -- error +from generate_series(1,5) x, + (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) +group by p order by p; + +select percentile_cont(0.5) within group (order by b) from aggtest; +select percentile_cont(0.5) within group (order by b), sum(b) from aggtest; +select percentile_cont(0.5) within group (order by thousand) from tenk1; +select percentile_disc(0.5) within group (order by thousand) from tenk1; +select rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); +select cume_dist(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); +select percent_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4),(5)) v(x); +select dense_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); + +select percentile_disc(array[0,0.1,0.25,0.5,0.75,0.9,1]) within group (order by thousand) +from tenk1; +select percentile_cont(array[0,0.25,0.5,0.75,1]) within group (order by thousand) +from tenk1; +select percentile_disc(array[[null,1,0.5],[0.75,0.25,null]]) within group (order by thousand) +from tenk1; +select percentile_cont(array[0,1,0.25,0.75,0.5,1]) within group (order by x) +from generate_series(1,6) x; + +select ten, mode() within group (order by string4) from tenk1 group by ten; + +select percentile_disc(array[0.25,0.5,0.75]) within group (order by x) +from unnest('{fred,jim,fred,jack,jill,fred,jill,jim,jim,sheila,jim,sheila}'::text[]) u(x); + +-- check collation propagates up in suitable cases: +select pg_collation_for(percentile_disc(1) within group (order by x collate "POSIX")) + from (values ('fred'),('jim')) v(x); + +-- ordered-set aggs created with CREATE AGGREGATE +select test_rank(3) within group (order by x) +from (values (1),(1),(2),(2),(3),(3),(4)) v(x); +select test_percentile_disc(0.5) within group (order by thousand) from tenk1; + +-- ordered-set aggs can't use ungrouped vars in direct args: +select rank(x) within group (order by x) from generate_series(1,5) x; + +-- outer-level agg can't use a grouped arg of a lower level, either: +select array(select percentile_disc(a) within group (order by x) + from (values (0.3),(0.7)) v(a) group by a) + from generate_series(1,5) g(x); + +-- agg in the direct args is a grouping violation, too: +select rank(sum(x)) within group (order by x) from generate_series(1,5) x; + +-- hypothetical-set type unification and argument-count failures: +select rank(3) within group (order by x) from (values ('fred'),('jim')) v(x); +select rank(3) within group (order by stringu1,stringu2) from tenk1; +select rank('fred') within group (order by x) from generate_series(1,5) x; +select rank('adam'::text collate "C") within group (order by x collate "POSIX") + from (values ('fred'),('jim')) v(x); +-- hypothetical-set type unification successes: +select rank('adam'::varchar) within group (order by x) from (values ('fred'),('jim')) v(x); +select rank('3') within group (order by x) from generate_series(1,5) x; + +-- divide by zero check +select percent_rank(0) within group (order by x) from generate_series(1,0) x; + +-- deparse and multiple features: +create view aggordview1 as +select ten, + percentile_disc(0.5) within group (order by thousand) as p50, + percentile_disc(0.5) within group (order by thousand) filter (where hundred=1) as px, + rank(5,'AZZZZ',50) within group (order by hundred, string4 desc, hundred) + from tenk1 + group by ten order by ten; + +select pg_get_viewdef('aggordview1'); +select * from aggordview1 order by ten; +drop view aggordview1; + -- variadic aggregates select least_agg(q1,q2) from int8_tbl; select least_agg(variadic array[q1,q2]) from int8_tbl; diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql index 7ea23de0b6a..c76882a3984 100644 --- a/src/test/regress/sql/create_aggregate.sql +++ b/src/test/regress/sql/create_aggregate.sql @@ -80,3 +80,24 @@ returns anyelement language sql as create aggregate least_agg(variadic items anyarray) ( stype = anyelement, sfunc = least_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 +); + +create aggregate my_rank(VARIADIC "any" ORDER BY VARIADIC "any") ( + stype = internal, + sfunc = ordered_set_transition_multi, + finalfunc = rank_final, + 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_* diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 1c71c964a5b..5cf58d5e575 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -26,6 +26,7 @@ SELECT ($1 = $2) OR EXISTS(select 1 from pg_catalog.pg_cast where castsource = $1 and casttarget = $2 and castmethod = 'b' and castcontext = 'i') OR + ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND EXISTS(select 1 from pg_catalog.pg_type where oid = $1 and typelem != 0 and typlen = -1)) @@ -38,6 +39,7 @@ SELECT ($1 = $2) OR EXISTS(select 1 from pg_catalog.pg_cast where castsource = $1 and casttarget = $2 and castmethod = 'b') OR + ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND EXISTS(select 1 from pg_catalog.pg_type where oid = $1 and typelem != 0 and typlen = -1)) @@ -567,14 +569,18 @@ SELECT * FROM funcdescs SELECT ctid, aggfnoid::oid FROM pg_aggregate as p1 -WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0 OR aggtransspace < 0; +WHERE aggfnoid = 0 OR aggtransfn = 0 OR + aggkind NOT IN ('n', 'o', 'h') OR + aggnumdirectargs < 0 OR + (aggkind = 'n' AND aggnumdirectargs > 0) OR + aggtranstype = 0 OR aggtransspace < 0; -- Make sure the matching pg_proc entry is sensible, too. SELECT a.aggfnoid::oid, p.proname FROM pg_aggregate as a, pg_proc as p WHERE a.aggfnoid = p.oid AND - (NOT p.proisagg OR p.proretset); + (NOT p.proisagg OR p.proretset OR p.pronargs < a.aggnumdirectargs); -- Make sure there are no proisagg pg_proc entries without matches. @@ -598,7 +604,9 @@ FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND a.aggtransfn = ptr.oid AND (ptr.proretset - OR NOT (ptr.pronargs = p.pronargs + 1) + OR NOT (ptr.pronargs = + CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 + ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) OR (p.pronargs > 0 AND @@ -607,7 +615,7 @@ WHERE a.aggfnoid = p.oid AND NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) OR (p.pronargs > 2 AND NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) - -- we could carry the check further, but that's enough for now + -- we could carry the check further, but 3 args is enough for now ); -- Cross-check finalfn (if present) against its entry in pg_proc. @@ -616,10 +624,19 @@ SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND a.aggfinalfn = pfn.oid AND - (pfn.proretset - OR NOT binary_coercible(pfn.prorettype, p.prorettype) - OR pfn.pronargs != 1 - OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); + (pfn.proretset OR + NOT binary_coercible(pfn.prorettype, p.prorettype) OR + NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR + CASE WHEN a.aggkind = 'n' THEN pfn.pronargs != 1 + ELSE pfn.pronargs != p.pronargs + 1 + OR (p.pronargs > 0 AND + NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) + OR (p.pronargs > 1 AND + NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) + OR (p.pronargs > 2 AND + NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) + -- we could carry the check further, but 3 args is enough for now + END); -- If transfn is strict then either initval should be non-NULL, or -- input type should match transtype so that the first non-null input @@ -685,18 +702,20 @@ WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND array_dims(p1.proargtypes) != array_dims(p2.proargtypes) ORDER BY 1; --- For the same reason, we avoid creating built-in variadic aggregates. - -SELECT oid, proname -FROM pg_proc AS p -WHERE proisagg AND provariadic != 0; - -- For the same reason, built-in aggregates with default arguments are no good. SELECT oid, proname FROM pg_proc AS p WHERE proisagg AND proargdefaults IS NOT NULL; +-- For the same reason, we avoid creating built-in variadic aggregates, except +-- that variadic ordered-set aggregates are OK (since they have special syntax +-- that is not subject to the misplaced ORDER BY issue). + +SELECT p.oid, proname +FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid +WHERE proisagg AND provariadic != 0 AND a.aggkind = 'n'; + -- **************** pg_opfamily **************** -- Look for illegal values in pg_opfamily fields |