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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!