Support ORDER BY within aggregate function calls, at long last providing a

non-kluge method for controlling the order in which values are fed to an
aggregate function.  At the same time eliminate the old implementation
restriction that DISTINCT was only supported for single-argument aggregates.

Possibly release-notable behavioral change: formerly, agg(DISTINCT x)
dropped null values of x unconditionally.  Now, it does so only if the
agg transition function is strict; otherwise nulls are treated as DISTINCT
normally would, ie, you get one copy.

Andrew Gierth, reviewed by Hitoshi Harada

5 files changed, 441 insertions, 8 deletions

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index a48b45007f3..9669a52fbea 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -529,3 +529,273 @@ select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
  9999 | 1
 (3 rows)
 
+--
+-- Test combinations of DISTINCT and/or ORDER BY
+--
+select array_agg(a order by b)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+ array_agg 
+-----------
+ {3,4,2,1}
+(1 row)
+
+select array_agg(a order by a)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+ array_agg 
+-----------
+ {1,2,3,4}
+(1 row)
+
+select array_agg(a order by a desc)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+ array_agg 
+-----------
+ {4,3,2,1}
+(1 row)
+
+select array_agg(b order by a desc)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+ array_agg 
+-----------
+ {2,1,3,4}
+(1 row)
+
+select array_agg(distinct a)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+  array_agg   
+--------------
+ {1,2,3,NULL}
+(1 row)
+
+select array_agg(distinct a order by a)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+  array_agg   
+--------------
+ {1,2,3,NULL}
+(1 row)
+
+select array_agg(distinct a order by a desc)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+  array_agg   
+--------------
+ {NULL,3,2,1}
+(1 row)
+
+select array_agg(distinct a order by a desc nulls last)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+  array_agg   
+--------------
+ {3,2,1,NULL}
+(1 row)
+
+-- multi-arg aggs, strict/nonstrict, distinct/order by
+select aggfstr(a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+                aggfstr                
+---------------------------------------
+ {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select aggfns(a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+                    aggfns                     
+-----------------------------------------------
+ {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select aggfstr(distinct a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+                aggfstr                
+---------------------------------------
+ {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select aggfns(distinct a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+                    aggfns                     
+-----------------------------------------------
+ {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select aggfstr(distinct a,b,c order by b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+                aggfstr                
+---------------------------------------
+ {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
+(1 row)
+
+select aggfns(distinct a,b,c order by b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+                    aggfns                     
+-----------------------------------------------
+ {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
+(1 row)
+
+-- test specific code paths
+select aggfns(distinct a,a,c order by c using ~<~,a)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+                     aggfns                     
+------------------------------------------------
+ {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
+(1 row)
+
+select aggfns(distinct a,a,c order by c using ~<~)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+                     aggfns                     
+------------------------------------------------
+ {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
+(1 row)
+
+select aggfns(distinct a,a,c order by a)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+                     aggfns                     
+------------------------------------------------
+ {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
+(1 row)
+
+select aggfns(distinct a,b,c order by a,c using ~<~,b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+                    aggfns                     
+-----------------------------------------------
+ {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+-- check node I/O via view creation and usage, also deparsing logic
+create view agg_view1 as
+  select aggfns(a,b,c)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                     pg_get_viewdef                                                                     
+--------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,3) i;
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                                       pg_get_viewdef                                                                                        
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c order by b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,3) i;
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                                              pg_get_viewdef                                                                                              
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(a,b,c order by b+1)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                              pg_get_viewdef                                                                               
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(a,a,c order by b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+select * from agg_view1;
+                     aggfns                     
+------------------------------------------------
+ {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                           pg_get_viewdef                                                                            
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(a,b,c order by c using ~<~)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                                      pg_get_viewdef                                                                                      
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+(1 row)
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c order by a,c using ~<~,b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,2) i;
+select * from agg_view1;
+                    aggfns                     
+-----------------------------------------------
+ {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+select pg_get_viewdef('agg_view1'::regclass);
+                                                                                                             pg_get_viewdef                                                                                                              
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
+(1 row)
+
+drop view agg_view1;
+-- incorrect DISTINCT usage errors
+select aggfns(distinct a,b,c order by i)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+LINE 1: select aggfns(distinct a,b,c order by i)
+                                              ^
+select aggfns(distinct a,b,c order by a,b+1)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+LINE 1: select aggfns(distinct a,b,c order by a,b+1)
+                                                ^
+select aggfns(distinct a,b,c order by a,b,i,c)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
+                                                  ^
+select aggfns(distinct a,a,c order by a,b)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
+LINE 1: select aggfns(distinct a,a,c order by a,b)
+                                                ^
diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out
index 1c540b2d956..448e319794c 100644
--- a/src/test/regress/expected/create_aggregate.out
+++ b/src/test/regress/expected/create_aggregate.out
@@ -32,6 +32,10 @@ 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;
@@ -39,7 +43,19 @@ create aggregate sum2(int8,int8) (
    sfunc = sum3, 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 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[],
+   initcond = '{}'
+);
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 91e0d2b04d7..0effa4b853d 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -571,6 +571,7 @@ SELECT user_relns() AS user_relns
  a_star
  abstime_tbl
  aggtest
+ aggtype
  array_index_op_test
  array_op_test
  arrtest
@@ -668,7 +669,7 @@ SELECT user_relns() AS user_relns
  toyemp
  varchar_tbl
  xacttest
-(101 rows)
+(102 rows)
 
 SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
  name 
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 39b0187a1e8..18f2e57b72b 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -230,3 +230,128 @@ select max(unique2) from tenk1 order by 1;
 select max(unique2) from tenk1 order by max(unique2);
 select max(unique2) from tenk1 order by max(unique2)+1;
 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
+
+--
+-- Test combinations of DISTINCT and/or ORDER BY
+--
+
+select array_agg(a order by b)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+select array_agg(a order by a)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+select array_agg(a order by a desc)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+select array_agg(b order by a desc)
+  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
+
+select array_agg(distinct a)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+select array_agg(distinct a order by a)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+select array_agg(distinct a order by a desc)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+select array_agg(distinct a order by a desc nulls last)
+  from (values (1),(2),(1),(3),(null),(2)) v(a);
+
+-- multi-arg aggs, strict/nonstrict, distinct/order by
+
+select aggfstr(a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+select aggfns(a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+
+select aggfstr(distinct a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+select aggfns(distinct a,b,c)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+
+select aggfstr(distinct a,b,c order by b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+select aggfns(distinct a,b,c order by b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,3) i;
+
+-- test specific code paths
+
+select aggfns(distinct a,a,c order by c using ~<~,a)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+select aggfns(distinct a,a,c order by c using ~<~)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+select aggfns(distinct a,a,c order by a)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+select aggfns(distinct a,b,c order by a,c using ~<~,b)
+  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+       generate_series(1,2) i;
+
+-- check node I/O via view creation and usage, also deparsing logic
+
+create view agg_view1 as
+  select aggfns(a,b,c)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,3) i;
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c order by b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,3) i;
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(a,b,c order by b+1)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(a,a,c order by b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(a,b,c order by c using ~<~)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+create or replace view agg_view1 as
+  select aggfns(distinct a,b,c order by a,c using ~<~,b)
+    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+         generate_series(1,2) i;
+
+select * from agg_view1;
+select pg_get_viewdef('agg_view1'::regclass);
+
+drop view agg_view1;
+
+-- incorrect DISTINCT usage errors
+
+select aggfns(distinct a,b,c order by i)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+select aggfns(distinct a,b,c order by a,b+1)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+select aggfns(distinct a,b,c order by a,b,i,c)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
+select aggfns(distinct a,a,c order by a,b)
+  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql
index 9d02048e245..61742482523 100644
--- a/src/test/regress/sql/create_aggregate.sql
+++ b/src/test/regress/sql/create_aggregate.sql
@@ -38,6 +38,10 @@ CREATE AGGREGATE newcnt ("any") (
    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 strict immutable;
@@ -47,6 +51,23 @@ create aggregate sum2(int8,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 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[],
+   initcond = '{}'
+);