Date: Tue, 22 Jun 2004 13:16:49 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Matthew Nuzum <matt@followers.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: matching rows differing only by fkey,pkey
Message-ID: <20040622181649.GB20086@wolff.to>
Mail-Followup-To: Matthew Nuzum <matt@followers.net>, pgsql-sql@postgresql.org
References: <20040622172704.GA19163@wolff.to>
	<200406221740.i5MHe6nb015623@ms-smtp-02.tampabay.rr.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200406221740.i5MHe6nb015623@ms-smtp-02.tampabay.rr.com>
User-Agent: Mutt/1.5.6i

On Tue, Jun 22, 2004 at 13:40:03 -0400,
  Matthew Nuzum <matt@followers.net> wrote:
>   The end result is to duplicate the data for a particular record in table
>   "a" so that all of it's related data in tables "b" and "c" is duplicated.
> Where "b" is the middle table in a many to many relationship.  For example,
> 
> - a ----    - b ----    - c ----
>  aid  <--+   bid  <--+   cid
>  data1   +-->aid     +-->bid
>  data2       field1      info1
>  date3       field2      info2
> 
> SELECT a.*,b.*,c.* from a,b,c where b.aid = a.aid and c.bid = b.bid;
> 
> So, the goal is to duplicate an object that is made up of the data stored
> across these three tables. 
> 
> Any suggestions?

First duplicate the record in table a. Its key will be in a sequence
named something like a_aid_seq.

Then for each record in table b with aid equal to the key of the record
being duplicated do the following:

Duplicate the current record in table b. Its aid should be
currval('a_aid_seq'). The new bid will be available in b_bid_seq.
The old bid will need to be noted by the program.
Then you can duplicate all of the records pointing to this record
in table c with something like the following:
INSERT INTO c (bid, info1, info2)
  SELECT currval('b_bid_seq'), info1, info2 FROM c WHERE bid = the_old_bid;

Offhand I can't think of a way to avoid using a procedural language to walk
through the b table so that you can easily keep track of which new bid
corresponds to which old bid. However, writing a perl script or plsql
function to do this for you shouldn't be difficult.