Re: [GENERAL] INSERT… RETURNING for copying records

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
>
>
>
>
>
>
>
>
>