Copy rows, remember old and new pkey

["Vlad K." <vlad@xxxxxxxxxxxxxx>]

Hello list,

I need to make a copy of certain rows in table A and remember the 
original pkey and new pkey in some temporary table B. Basically the copy 
statement is    INSERT INTO a SELECT * FROM a where a.x=y;

I guess I can do it with a plpgsql function and a FOR loop statement, 
because I need two insert statements, the second using returned pkey 
from first, but I was wondering if there's a simpler way, perhaps using 
WITH?

FOR row IN SELECT * FROM a WHERE a.x=123 LOOP
    INSERT INTO a (x, y, z) VALUES (row.x, row.y, row.z) RETURNING pkey 
INTO new_pkey;
    INSERT INTO b (old_id, new_id) VALUES (row.pkey, new_pkey);
END LOOP;


Thanks,

--


.oO V Oo.


Work Hard,
Increase Production,
Prevent Accidents,
and
Be Happy!  ;)



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general