Ghostman Ghostman - 3 years ago 197
SQL Question

Group_concat equivalent in postgresql 8.2.11

I am using a older version of Postgres 8.2.11. Can anyone tell me the equivalent of MySql's

group_concat
for this Postgres 8.2.11. I have tried
array_accum
,
array_to_string
,
string_agg
but it doesn't work in this version

Answer Source

The "not quite duplicate" in the comments should point you in the right direction: create your own aggregate function. First you'll need a non-aggregate string concatenation function, something like this:

create function concat(t1 text, t2 text) returns text as $$
begin
    return t1 || t2;
end;
$$ language plpgsql;

Then you can define your own aggregate version of that function:

create aggregate group_concat(
    sfunc    = concat,
    basetype = text,
    stype    = text,
    initcond = ''
);

Now you can group_concat all you want:

select group_concat(s)
from t
group by g

I dug this out of my archives but I think it should work in 8.2.

Keep in mind that 8.2 is no longer supported so you might want to upgrade to at least 8.4 as soon as possible.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download