SQL creating a concatenated string of names from an array of ids
I am developing reports using Streamtask with xReporter. Recently, our databases were updated to allow for multiple projects to be assigned to a single task. To achieve this, a string containing a a set of CSV index values was used to replace the old single index ie: '[1,2,3,4]' instead of 1. As a result we now have to update a slew of reports.
To get around this, I replace the original selection statement:
(select prj.name from project_table prj where prj.id=t.project_id) as project,
with this line:
(select prj.name from project_table prj where prj.id = ANY((string_to_array(btrim(task_table.projects, ''), ','))::integer)) as project,
which turns the string (successfully) into an array of integer values.
The problem is that I must be able to take this array of indices and retrieve each project name associated with each index, and concatenate them together into a string containing a list of the project names.
Because I have to implement this in an xReporter report, I believe I will need an SQL-only solution to my problem.
Storing comma separated values in a relation database is a really horrible anti-pattern.
Postgres has some really nifty functions to workaround this design-error which makes the query actually quite easy (but probably not really fast)
select prj.name, tsk.id as task_id from project_table prj join ( select id, regexp_split_to_table(projects, ',')::int as pid from task_table ) tsk on tsk.pid = prj.id order by prj.id
You original statement looks like you were really embedding that into a bigger statement, so I'm not sure how suitable the join is. But you could always create a normalized view on the mis-designed table using the above approach (the inner derived table).
Here is a SQLFiddle: http://sqlfiddle.com/#!15/6f9bb/1