Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6F7B79FB63C for ; Sat, 21 Apr 2007 23:18:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68292-08 for ; Sat, 21 Apr 2007 23:18:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by postgresql.org (Postfix) with ESMTP id 203209FB5B9 for ; Sat, 21 Apr 2007 23:18:37 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1447117ana for ; Sat, 21 Apr 2007 19:18:36 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=qjAfeDqVsSnvWKTbSmzdgX61iixzO8nLc9xNrmd7d5wNSAUMcMdcemV0zFMcIMuFdJH35qFVZZ1P4Xge9+FE+xUyiY2h3fI4rV+858pR1/lCa2cqO4P+oGtd5Rs+FtEHicyxCn1Jrv5rf+iZqGXyIZFB03mEufgDeJAOU+GF6gQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=hUL5VVEdGXvg2EZsM+vzT4i5PjT/9sbzKHhp1Sdnte5c0KdU+r5K6iyz/atVc+X+K8gc5xObiwDIaf9497Sow5DrLSqnveSB2c50dmVQi3/JTCg3ttsARMPx2nyRjOdw0ud1bQ0lNTj9MFr9UMcq+KTQi0x31Ueuecvwb0Ipfn8= Received: by 10.100.178.7 with SMTP id a7mr2695083anf.1177208316671; Sat, 21 Apr 2007 19:18:36 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Sat, 21 Apr 2007 19:18:36 -0700 (PDT) Message-ID: Date: Sat, 21 Apr 2007 21:18:36 -0500 From: "Aaron Bono" To: "Tom Lane" Subject: Re: Split String Into Multiple Records Cc: "Lista PostgreSQL SQL" In-Reply-To: <16271.1177208012@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_162285_4693310.1177208316527" References: <16271.1177208012@sss.pgh.pa.us> X-Google-Sender-Auth: 072c5008220c08ad X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/204 X-Sequence-Number: 28204 ------=_Part_162285_4693310.1177208316527 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/21/07, Tom Lane wrote: > > "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. > > 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 ================================================================== ------=_Part_162285_4693310.1177208316527 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_162285_4693310.1177208316527--