pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Split String Into Multiple Records
3+ messages / 2 participants
[nested] [flat]

* Split String Into Multiple Records
@ 2007-04-21 23:47  Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Aaron Bono @ 2007-04-21 23:47 UTC (permalink / raw)
  To: pgsql-sql

Is there a good way to split a string into multiple records?

Here is what I am trying to do...

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.

branch {
    branch_id bigserial primary key,
    branch_num varchar(255)
}

branch_area {
    branch_area_id bigserial primary key,
    branch_id bigint foreign key to branch,
    branch_num varchar(10)
}

I want to migrate the data something like this:

insert into branch_area
(branch_id, branch_num)
select
    branch_id,
    -- This is the part I need help with -> split branch.branch_num on ','
from branch
;

Is there a good way (or alternative way) to do this?

Thanks!

-- 
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Split String Into Multiple Records
@ 2007-04-22 02:13  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2007-04-22 02:13 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql

"Aaron Bono" <postgresql@aranya.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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Split String Into Multiple Records
@ 2007-04-22 02:18  Aaron Bono <postgresql@aranya.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Aaron Bono @ 2007-04-22 02:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On 4/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Aaron Bono" <postgresql@aranya.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
==================================================================

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2007-04-22 02:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-21 23:47 Split String Into Multiple Records Aaron Bono <postgresql@aranya.com>
2007-04-22 02:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-22 02:18   ` Aaron Bono <postgresql@aranya.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox