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. Hi David,
I am not sure the RETURNING offers you the following behavior ..
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
On Sat, Sep 8, 2012 at 12:49 AM, David Johnston <polobo@xxxxxxxxx> 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.
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 |
+-----+----------+---------+-----------+ 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?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
|
