On 4/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes: > Is there a good way to split a string into multiple records? > I have a table "branch" with a column "branch_num" which has a comma > delimited list of numbers - the users weren't supposed to do this but they > did and now I have to fix it. We want to create a new table "branch_area" > and move this comma delimited list into this new table as multiple records > before dropping the "branch_num" from the "branch" table. 8.3 will have some nifty functions for that, but in existing releases you're on your own. I'd think about making a plpgsql function that loops around split_part() and returns each chunk with RETURN NEXT. One problem with that is that you'd want to invoke it like so: insert into branch_area ... select my_split_func(branch_num) from branch; and plpgsql doesn't support invoking set-returning functions this way. But you can get around that with a SQL-language wrapper function. It's pretty grotty on the whole, but should do for a one-time problem. BTW, check the archives, because I think this type of problem has been discussed before --- somebody may have already posted usable code.
I'll just do it by hand. There isn't much data right now. I will keep an eye open for the new features though. Thanks! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==================================================================
