Re: Split String Into Multiple Records

"Aaron Bono" <postgresql(at)aranya(dot)com> 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.

regards, tom lane