Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6B4F69FB5B9 for ; Sat, 21 Apr 2007 23:13:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 72816-04 for ; Sat, 21 Apr 2007 23:13:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D5B739FB60D for ; Sat, 21 Apr 2007 23:13:35 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3M2DWHe016272; Sat, 21 Apr 2007 22:13:32 -0400 (EDT) To: "Aaron Bono" cc: "Lista PostgreSQL SQL" Subject: Re: Split String Into Multiple Records In-reply-to: References: Comments: In-reply-to "Aaron Bono" message dated "Sat, 21 Apr 2007 18:47:20 -0500" Date: Sat, 21 Apr 2007 22:13:32 -0400 Message-ID: <16271.1177208012@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/203 X-Sequence-Number: 28203 "Aaron Bono" 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