SEO

This is not a question, but a solution. I just wanted to share this
with others on the list in case it saves you a few hours of searching...

I wanted to select several rows of data and have them returned in a
single record with the rows joined by a delimiter. Turns out this is
very easy to do in PostgreSQL:

SELECT a.id, a.name,
ARRAY_TO_STRING(ARRAY(
SELECT b.name
FROM b
WHERE b.id = a.id
ORDER BY b.name ASC
), ',') AS b_names
FROM a
ORDER BY a.id ASC;

Sample data would look like this:

[table a]
id | name
----+------
1 | one
2 | two
3 | three
4 | four

[table b]
id | name
----+------
1 | pizza
1 | hot dog
2 | gorilla
2 | monkey
3 | apple
4 | cheese
4 | milk
4 | eggs

And the result would look like this:

id | name | b_names
----+-------+---------
1 | one | pizza,hot dog
2 | two | gorilla,monkey
3 | three | apple
4 | four | cheese,milk,eggs

This is an easy way to return attributes of a record from another table
without having to issue multiple queries or deal with multiple result
records.

Enjoy!

-- Dante


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

Le lundi 02 juillet 2007, D. Dante Lorenso a écrit*:
> I wanted to select several rows of data and have them returned in a
> single record with the rows joined by a delimiter. Turns out this is
> very easy to do in PostgreSQL:
>
> SELECT a.id, a.name,
> ARRAY_TO_STRING(ARRAY(
> SELECT b.name
> FROM b
> WHERE b.id = a.id
> ORDER BY b.name ASC
> ), ',') AS b_names
> FROM a
> ORDER BY a.id ASC;

In this case I've used this pretty simple custom aggregate:

CREATE AGGREGATE array_acc (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);

The query would become
SELECT a.id, a.name, array_acc(b.name) as b_names
FROM a LEFT JOIN b USING(id)
GROUP BY a.id, a.name;

The b_names column will have this kind of data: {cheese,milk,eggs}, you could
use array_to_string(array_acc(b.name)) as b_names to obtain what you're
already dealing with.

Hope this helps,
--
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBGig0MlBXRlnbh1bkRAj59AJ9Y5J9nIJncWDoZuKmeIi r9CXbLigCeImd0
valGY9LdxCEY5wV3bbQyvMs=
=FsZS
-----END PGP SIGNATURE-----

> The query would become
> SELECT a.id, a.name, array_acc(b.name) as b_names
> FROM a LEFT JOIN b USING(id)
> GROUP BY a.id, a.name;
>

All variants are possible. Variant with array_to_string is faster and
doesn't need own aggregate function. And you can easy remove
duplicities.

SELECT a.id, a.name,
ARRAY_TO_STRING(ARRAY(
SELECT DISTINCT b.name
FROM b
WHERE b.id = a.id
ORDER BY b.name ASC
), ',') AS b_names
FROM a
ORDER BY a.id ASC;

regards
Pavel

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster