Re: Split String Into Multiple Records

On 4/21/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "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.
>
>
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
==================================================================