Re: Sorting aggregate column contents

People from brazilian postgresql list sent me another way to sort the
column contents. The way is the aggregate accumulate values and after
run an array sort function. It worked fine. I think the subquery in
function f_select_array does not decrease performance. Follow the
code:

--from http://archives.postgresql.org/pgsql-general/2005-12/msg01093.php
CREATE FUNCTION f_select_array(anyarray) RETURNS SETOF anyelement AS $$
BEGIN
FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP
RETURN NEXT $1[i];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE FUNCTION f_sort_array(anyarray) RETURNS anyarray AS $$
SELECT array(SELECT * FROM f_select_array($1) ORDER BY 1)
$$ LANGUAGE sql IMMUTABLE STRICT;

-- from http://www.postgresql.org/docs/8.1/interactive/xaggr.html
CREATE AGGREGATE ag_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

select e.sigla, array_to_string(f_sort_array(ag_accum(m.nome)), ', ')
AS municipio from estado e
inner join municipio m on (m.idestado = e.idestado)
group by e.sigla
order by e.sigla;
/* result */
sigla | municipio
-------+-----------------------
RS | Gramado, Porto Alegre
SP | Osasco
(2 rows)

--
Everton

On 5/3/06, Everton Luís Berz <everton(dot)berz(at)gmail(dot)com> wrote:
> Thanks a lot the explanation.
>
> I tested all cases and I noticed that reordering the source table (city)
> not works on all cases, so I think Postgresql perform different internal
> sort to optimize some query's.
> I noticed this in other query I performed:
>
> select s.ano,
> s.semestre,
> dhc.iddisciplinahorariocurriculo,
> count(*),
> ag_concatenar_com_quebra_de_linha(td.turno) AS turno
> from disciplinahorariocurriculo dhc
> inner join horariocurriculo hc on (hc.idhorariocurriculo =
> dhc.idhorariocurriculo)
> inner join semestre s on (s.idsemestre = hc.idsemestre)
> inner join (select tdinterno.iddisciplinahorariocurriculo, t.turno from
> turnodisciplina tdinterno
> inner join turno t on (t.idturno = tdinterno.idturno)
> order by tdinterno.iddisciplinahorariocurriculo, t.turno) as
> td on (td.iddisciplinahorariocurriculo = dhc.iddisciplinahorariocurriculo)
> -- where dhc.iddisciplinahorariocurriculo = 8282
> group by 1, 2, 3
> having count(*) > 1
> order by 1, 2, 3;
>
> ano | semestre | iddisciplinahorariocurriculo | count | turno
> ...
> 2004 | 2 | 8282 | 3 | 23, 63, 43
> ^ ^ ^
> ...
>
> If I remove the comment in the 'where' line there is the right result:
> ano | semestre | iddisciplinahorariocurriculo | count | turno
> ------+----------+------------------------------+-------+------------
> 2004 | 2 | 8282 | 3 | 23, 43, 63
> ^ ^ ^
> (1 row)
>
>
> I didn't know the array_to_string way, I think I will use it. It's safe
> and easy.
>
> Regards,
> --
> Everton
>
>
> Ben K. escreveu:
> >> It works fine. But I wouldn't like using subselect's, then if somebody
> >> else
> >> knows about an operator or something like that to put on the
> >> aggregator, please tell me.
> >
> >
> >
> > I think the nature of the f_concat makes it difficult to sort, since it
> > simply adds the next value, so if the source table gives value in the
> > order of 'a','c','d','b' there's no way to handle them within f_concat
> > unless you modify and rearrange the previous result string from within
> > f_concat.
> >
> >
> > So the source table (city) should be sorted. I don't know if this is a
> > standard way, but this one seems to do that.
> >
> >
> > ======================================================
> > select s.name, ag_concat(c.name) from state s inner join (select * from
> > city order by name desc) as c on c.idstate=s.idstate group by s.name
> > order by 1;
> >
> > OR
> >
> > select s.name, ag_concat(c.name) from state s, (select * from city order
> > by name desc) as c where c.idstate = s.idstate group by s.name order by 1;
> > ======================================================
> >
> >
> > I'm just reordering the source table on the fly. Curiously, if you don't
> > have 'desc' you'll get a reverse ordered list. (z,...,a)
> >
> > I think your needs may also be met without any aggregator as well (there
> > may be marginal cases which I haven't thought of, but I assume they can
> > be handled if needed)
> >
> > ======================================================
> > select s.name, array_to_string(array(select name from city where idstate
> > = s.idstate order by name),',') from state s;
> > ======================================================
> >
> > name | array_to_string
> > ------+---------------------
> > RP | Gramado,Port Alegre
> > SP | Osasco
> >
> >
> > * I see normalization issue here but guess it's not important.
> >
> >
> >
> > Regards,
> >
> > Ben K.
> > Developer
> > http://benix.tamu.edu
> >
>

--
Everton