Re: [GENERAL] INSERT… RETURNING for copying records

[Misa Simic <misa.simic@xxxxxxxxx>]

You can make function what returns integer and has input parametars as other columns of the table:

INSERT INTO testing (category, name, fk_parent) (input parameters) returning rid

Then SELECT rid as OriginalId, make_copy(other columns) as new_rid From testing

Kind Regards,

Misa

On Friday, September 7, 2012, Michael Sacket wrote:

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