looking for alternative to MySQL's GROUP_CONCAT function

[x***@yahoo.com]

Hi all,

I'm looking into PostgreSQL. Coming from a MySQL
background, I have made heavy use of its very useful
GROUP_CONCAT function. You can read about the function
here:

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

Is there a PostgreSQL alternative to GROUP_CONCAT?

Thanks,
Ken


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Magnus Hagander]

Post by x***@yahoo.com
Hi all,
I'm looking into PostgreSQL. Coming from a MySQL background,
I have made heavy use of its very useful GROUP_CONCAT
function. You can read about the function
http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
Is there a PostgreSQL alternative to GROUP_CONCAT?

Check the user comments at
http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html.
Sounds like what you're looking for.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by x***@yahoo.com
I'm looking into PostgreSQL. Coming from a MySQL
background, I have made heavy use of its very useful
GROUP_CONCAT function. You can read about the function
http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
Is there a PostgreSQL alternative to GROUP_CONCAT?

In Postgres it's customary to build this sort of thing out of spare
parts. The basic spare part is a user-defined aggregate. You can
make an aggregate over the built-in concatenation function:

CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat,
STYPE = text
);

This does the basic task of concatenating the values found in a table
group, but it doesn't supply any separator, so more likely you'd want
to write a custom function that inserts a separator and then aggregate
with that:

create function textcatspace(text,text) returns text as $$
select $1 || ' ' || $2
$$ language sql strict immutable;

CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcatspace,
STYPE = text
);

(NB: the "strict" bit is essential to get this to match MySQL's
semantics for NULLs in group_concat.)

We don't unfortunately have any easy way to pass in different separator
values --- you'd need to create a distinct function and aggregate for
each separator string you want. Also, a function in plpgsql might be
more efficient than one in SQL.

The other part this isn't handling is controlling the order in which the
inputs are concatenated. You can find that discussed in the archives:

http://archives.postgresql.org/pgsql-general/2005-09/msg00034.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[David Fetter]

Post by x***@yahoo.com
Hi all,
I'm looking into PostgreSQL. Coming from a MySQL background, I have
made heavy use of its very useful GROUP_CONCAT function. You can
http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
Is there a PostgreSQL alternative to GROUP_CONCAT?

There's a more flexible one :)

Use the array_accum() aggregate as described here

http://www.postgresql.org/docs/current/static/xaggr.html

and wrap it with array_to_string()

http://www.postgresql.org/docs/current/static/functions-array.html

to get a nice, flexible system. You can make a static one if you
like. One thing about this approach is that you then have to exclude
NULLs from your search because PostgreSQL arrays can't yet hold NULLs.

Cheers,
D
--
David Fetter ***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match