Maybe: Where newvals AS () , insertval AS (insert...select...from newvals) #NO RETURNING Select * from newvals
I believe the insertval CTE is guaranteed to run even if not directly involved with the main select statement. David J. On Sep 8, 2012, at 2:33, dinesh kumar <dineshkuma...@gmail.com> wrote: > Hi David, > > I am not sure the RETURNING offers you the following behavior .. > > < What I'm looking for > > > +--------------+-----+ > > | original_rid | rid | > > +--------------+-----+ > > | 1 | 4 | > > | 2 | 5 | > > | 3 | 6 | > > +--------------+-----+ > > > > I believe, the following example gives you the desired results once we insert > completes.. > > > > postgres=# SELECT * FROM TEST; > t | t1 > ---+-------- > 1 | Dinesh > 2 | Dinesh > 3 | Kumar > 4 | Kumar > 5 | Manoja > (5 rows) > > postgres=# SELECT MIN(T),MAX(T),T1 FROM TEST GROUP BY T1 HAVING > MIN(T)!=MAX(T); > min | max | t1 > -----+-----+-------- > 1 | 2 | Dinesh > 3 | 4 | Kumar > (2 rows) > > Best Regards, > Dinesh > manojadinesh.blogspot.com > > > On Sat, Sep 8, 2012 at 12:49 AM, David Johnston <pol...@yahoo.com> wrote: > > > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Sacket > Sent: Friday, September 07, 2012 2:09 PM > To: PG-General Mailing List > Subject: [GENERAL] INSERT… RETURNING for copying records > > > > Good Afternoon, > > > > I'm attempting to write a function that will duplicate a few records, but the > catch is I need to have a mapping of the original pk to the new pk. I know I > can use the RETURNING clause to get the new ids... but how to map that to the > original ones is escaping me. > > > > < Setup > > > > > CREATE TABLE testing (rid serial PRIMARY KEY, category text NOT NULL, name > text NOT NULL, fk_parent int4); > > > > INSERT INTO testing (category, name, fk_parent) VALUES ('cat1', 'one', NULL), > ('cat1', 'one.one', 1), ('cat1', 'one.two', 1); > > > > SELECT * FROM testing; > +-----+----------+---------+-----------+ > | rid | category | name | fk_parent | > +-----+----------+---------+-----------+ > | 1 | cat1 | one | NULL | > | 2 | cat1 | one.one | 1 | > | 3 | cat1 | one.two | 1 | > +-----+----------+---------+-----------+ > > > > < Duplicating the records > > > > > INSERT INTO testing (category, name, fk_parent) (select category, name, > fk_parent from testing where category='cat1') returning rid, category, name, > fk_parent; > > +-----+----------+---------+-----------+ > > | rid | category | name | fk_parent | > > +-----+----------+---------+-----------+ > > | 4 | cat1 | one | NULL | > > | 5 | cat1 | one.one | 1 | > > | 6 | cat1 | one.two | 1 | > > +-----+----------+---------+-----------+ > > > > < What I'm looking for > > > +--------------+-----+ > > | original_rid | rid | > > +--------------+-----+ > > | 1 | 4 | > > | 2 | 5 | > > | 3 | 6 | > > +--------------+-----+ > > > > < This doesn't work > > > > > INSERT INTO testing (category, name, fk_parent) select category, name, > fk_parent from testing as original where category='cat1' returning rid, > category, name, fk_parent, original.rid; > > > > > > Specifically, my goal is to be able to duplicate a subset of records and map > any referenced foreign keys to the new ones from the copies. I could write a > pl/pgsql function to loop through the records and build the mapping as I go, > but I was thinking there might be a better way. Any thoughts? > > > > Thanks! > > Michael > > > > > > > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > > > > Two thoughts (syntax not validated): > > > > INSERT INTO …. VALUES (non-id-cols, id) > > SELECT [non-id-cols], nextval(‘sequence’) AS new_id FROM testing > > RETURNING id, new_id > > > > There is no reason to delay the assignment of the ID until the time of > insert; by polling the sequence manually you get the same effect but at a > time when you have not forgotten what the old value was. > > > > If for some reason you have to let the ID be auto-generated you likely need > to identify the “natural key” for the record and then: > > > > WITH ins ( > > INSERT …. RETURNING newid, (natural_key_cols) AS natrualkey > > ) > > SELECT * > > FROM ins > > JOIN testing ON > > ins.naturalkey = (testing.natural_key cols) > > > > If there is no natural key then this method is ambiguous in the presence of > multiple otherwise identical records. > > > > David J > >
