[pgsql] łączenie stringów w jeden (sum(pole::text)) - rozwiązanie problemu

[hubert depesz lubaczewski]

hej
może kiedyś mieliście taką sytuację, że chcieliście połączyć kilka
stringów w jeden:
select * from tabelka;
x | nazwa
---+-------
1 | a
1 | b
1 | c
2 | d
2 | e
3 | f

tak, aby uzyskać:
x | nazwy
---+---------
1 | a, b, c
2 | d, e
3 | f

ponieważ potrzebowaliśmy coś takiego w firmie, napisałem i chciałbym
udostępnić.
rozwiązanie:

--- cut here ---
CREATE OR replace function fn_text_sum(TEXT, TEXT) RETURNS TEXT AS '
DECLARE
txt_before ALIAS FOR $1;
txt_new ALIAS FOR $2;
use_txt_new TEXT;
txt_divider TEXT;
pos_divider INT4;
BEGIN
pos_divider := position(''`'' in txt_new);
IF (pos_divider = 0) THEN
txt_divider := '', '';
use_txt_new := txt_new;
ELSE
txt_divider := substr(txt_new, 1, pos_divider - 1);
use_txt_new := substr(txt_new, pos_divider + 1);
END IF;
IF (txt_before IS NULL) THEN
RETURN use_txt_new;
END IF;
RETURN txt_before || txt_divider || use_txt_new;
END;
' language 'plpgsql';

CREATE aggregate sum (
basetype = TEXT,
sfunc = fn_text_sum,
stype = TEXT
);

--- cut here ---

i od tej pory można:
select x, sum(nazwa) as nazwy from tabelka group by x;

zwracam uwagę iż nazwy w polu "nazwy" są zasadniczo w losowej kolejności.
aby były posortowane musimy użyć podzapytania:

> select x, sum(nazwa) from (select x, nazwa from x order by x, nazwa) y group by x;

dodatkowo można użyć tej funkcji by łączyła kolejne stringi czymś innym niż ", ":
> select x, sum('; `'||nazwa) from x group by x;
x | sum
---+---------
1 | a; b; c
2 | d; e
3 | f

niestety możliwość zmiany tego stringu (łączącego) jest dosyć nieładnym
"hack'iem".
działa to tak, że nowy sum() sprawdza czy w stringu jest znak "`"
(odwrotny apostrof) i jeśli tak, to traktuje to co przed nim jako
"divider", a to co za nim za właściwe pole.
niestety może to prowadzić do pewnych błędów w sytuacji gdyby w jakimś
polu po prostu był odwrócony apostrof:
> insert into tabelka (x, nazwa) values ('1', 'test`apostrofu');
INSERT 1100256 1
> select x, sum(nazwa) from x group by x;
x | sum
---+----------------------
1 | a, b, ctestapostrofu
2 | d, e
3 | f

z tego względu zalecam aby zawsze podawać łącznik - unikniemy wtedy
problemu:
> select x, sum(', `'||nazwa) from x group by x;
x | sum
---+-------------------------
1 | a, b, c, test`apostrofu
2 | d, e
3 | f

mam nadzieję iż komuś się to przyda.

depesz

--
napisanie do mnie na priv daje 99.9% gwarancję braku odpowiedzi
*-----------------------------------------------------------------*
sklep z rzeczami do domu: http://ulek.net/

[saint]

a może lepiej tak:

CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);

SELECT textcat_all(nazwa || ';') ) from x group by x;

[hubert depesz lubaczewski]

saint wyrzeźbił(a):

fajne, ale wolę moje - nie dodaje separatora na końcu.
oczywiście mogę go później wyciąć substr'em czy czymś takim, ale wolę
mieć to gotowe.

[saint]

hubert depesz lubaczewski wrote:

>fajne, ale wolę moje - nie dodaje separatora na końcu.
>oczywiście mogę go później wyciąć substr'em czy czymś takim, ale wolę
>mieć to gotowe.
>
>

Wystarczy: rtrim.

s.

[Ronald Kuczek]

Dzięki za typ. Od zawsze podziwiam Postgresa za jego możliwości,
to kolejny przykład, że z tego silnika można wycisnąć naprawdę baaardzo
dużo.

Pozdrawiam
Rony