On Mon, Nov 26, 2012 at 3:35 AM, Vlad K. <vlad@xxxxxxxxxxxxxx> wrote: > > 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; I don't think you need a CTE or a loop unless you want to delete the old rows. Why not (hard to say exactly how it will work): INSERT INTO a(old_id, new_id, ...) SELECT id, new_id(), ... from a; ? merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
