Copy rows, remember old and new pkey

On Mon, Nov 26, 2012 at 3:35 AM, Vlad K. <vlad@haronmedia.com> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/26/2012 07:15 PM, Merlin Moncure wrote:

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

Well, the table A, in which I'm copying rows, does not have old_id,
new_id, just a primary key, so the idea is to link original pkey and new
pkey separately. Adding new_id to the table is not an option, besides
what would new_id() stand for?

But thanks for the suggestion.

--

.oO V Oo.

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

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