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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
