String them up#

Wed, 09 Jul 2008 19:30:28 +0000

Postgresql has no builtin support for converting a multi-row result set (e.g. select code from country) into a single string (e.g. AF,AL,DZ,AD,AI,AQ,AG,AR,AM... ). Standard advice is to use CREATE AGGREGATE to make one, and (my) standard response to that advice is to grumble about its absence from the standard functions.

However, the little-used Postgres "array" type offers an alternative approach, as witness:

sql> select arraytostring(array(select code from country limit 5),',');
arraytostring
-----------------
UK,US,FR,DE,AF

Sha and jolly well zaam. I've worked this out once before, I know, but forgotten it entirely. Here's hoping that writing it down will fix it in my mind.

I have very nearly full range of motion (not all of it absolutely pain free) in my left arm, but I can't quite get it to touch the left side of my head. Went skating last night (route check) for the first time in five weeks, and got home feeling utterly knackered.