pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSplit 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>
2007-04-22 02:13 ` Re: Split String Into Multiple Records Tom Lane <tgl@sss.pgh.pa.us>
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-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 ` Re: Split String Into Multiple Records 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-21 23:47 Split String Into Multiple Records Aaron Bono <postgresql@aranya.com>
2007-04-22 02:13 ` Re: Split String Into Multiple Records Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-22 02:18 ` Aaron Bono <postgresql@aranya.com>
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