Re: Aggregates, group, and order by

On Mon, Nov 07, 2005 at 05:12:05PM +0900, Michael Glaesmann wrote:
> I'm trying to concatenate strings in variable orders using a custom
> aggregate. However, I'm having a difficult time figuring out the
> SQL I need to use to accomplish this.

How about using the ARRAY() constructor as below?

> Here's a test case that
> shows the error I'm getting.
>
> select version();
>
> version
> ------------------------------------------------------------------------
> ----------------------------------------------------------------------
> PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
> powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
> build 5026)
> (1 row)
>
>
> create table ordered_foo (
> foo_id integer not null
> , bar_id integer not null
> , foo_value text not null
> , foo_pos integer not null
> , unique (foo_id, bar_id)
> ) without oids;
>
> copy ordered_foo (foo_id, bar_id, foo_value, foo_pos) from stdin;
> 1 1 delta 4
> 2 1 alpha 1
> 3 1 charlie 3
> 4 1 bravo 2
> 5 2 C 3
> 6 2 B 2
> 7 2 A 1
> 8 2 D 4
> \.
>
> CREATE AGGREGATE array_accum (
> sfunc = array_append,
> basetype = anyelement,
> stype = anyarray,
> initcond = '{}'
> );
>
> select bar_id, array_accum(foo_value)
> from ordered_foo
> group by bar_id
> order by bar_id;
> bar_id | array_accum
> --------+-----------------------------
> 1 | {delta,alpha,charlie,bravo}
> 2 | {C,B,A,D}

SELECT DISTINCT
o1.bar_id,
ARRAY(
SELECT o2.foo_value
FROM ordered_foo o2
WHERE o1.bar_id = o2.bar_id
ORDER BY o2.foo_value
)
FROM ordered_foo o1
ORDER BY o1.bar_id;

bar_id | ?column?
--------+-----------------------------
1 | {alpha,bravo,charlie,delta}
2 | {A,B,C,D}
(2 rows)

Another way to do this could be with another function like this.

CREATE OR REPLACE FUNCTION array_sort(in_array ANYARRAY)
RETURNS ANYARRAY
LANGUAGE plpgsql
AS $$
BEGIN /* Yes, I know I'm not checking array dimensions. This is a prototype. */
RETURN ARRAY (
SELECT in_array[s.i]
FROM generate_series(
array_lower(in_array,1),
array_upper(in_array,1)
) AS s(i)
ORDER BY 1
);
END;
$$;

> [snip]
>
> I can't think of a way to push the aggregate with the order by into a
> subquery that still produces the proper grouping (by bar_id). Any
> hints or suggestions?

HTH :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!