Monday, November 14, 2011

Postgresql - array / array_to_string function (packing a subquery intoa single field)

Just for the sake of it, I wanted to concatenate the result set of a subquery into a single string field . I never thought it would be this easy!:

[sourcecode language="sql"]

CREATE OR REPLACE VIEW musicbrainz.artistas_mexicanos AS
SELECT a.id AS id_artista, an.name AS nombre,
array_to_string(array(select trim(artn.name::varchar(100)) from artist art left join artist_alias aa on aa.artist = art.id and art.name = aa.name
left join artist_name artn on aa.name = artn.id where art.id = a.id
),',') as alias, at.name AS tipo_artista, g.name AS genero, c.name AS pais
FROM artist a
LEFT JOIN artist_name an ON an.id = a.name
LEFT JOIN country c ON c.id = a.country
LEFT JOIN gender g ON g.id = a.gender
LEFT JOIN artist_type at ON at.id = a.type
WHERE c.name::text = 'Mexico'::text;

The cute part being:

array_to_string(array(select trim(artn.name::varchar(100)) from artist art left join artist_alias aa on aa.artist = art.id and art.name = aa.name
left join artist_name artn on aa.name = artn.id where art.id = a.id
),',')

[/sourcecode]





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

No comments:

Post a Comment