From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Michael Sacket 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; < 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 |
