Re: RE: [GENERAL] INSERT. RETURNING for copying records

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 <dineshkumar02(at)gmail(dot)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 <polobo(at)yahoo(dot)com> wrote:
>
>
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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
>
>