A simple pg/plsql loop example

You can use:

UPDATE advertiser a SET partnerid = r.partneridFROM partner_advertiser rWHERE a.id = r.advertiserid

Generally, simple relational transformations like these will never need a loop.If you really need one, though, look at http://www.postgresql.org/docs/9.0/interactive/plpgsql-control-structures.html

One extra note: any transformation will obviously lose data if advertiserid is not already unique, thus you should first run something like

SELECT count(*), advertiserid FROM partner_advertiserGROUP BY advertiserid HAVING COUNT(*) > 1

If any rows get returned by that you will want to fix that manually.

This also works. Using a simple plpgsql snippet:

CREATE OR REPLACE FUNCTIOn migratePartnerAdvertiser() RETURNS int4 AS 'DECLARE r RECORD;BEGIN    FOR r IN SELECT * from partner_advertiser LOOP            UPDATE advertiser SET partnerId = r.partnerId WHERE id = r.advertiserId;     END LOOP;return 1;END;' LANGUAGE plpgsql;SELECT migratePartnerAdvertiser() as output;

DECLARE id integer;r record;BEGIN<BR>INSERT INTO tabla1(campo1, campo2) VALUES(UPPER(d1),UPPER(d2));<BR>id = (SELECT LASTVAL());<BR>FOR r IN SELECT mi_campo FROM tabla2 LOOP<BR>    INSERT INTO tabla3(campo1, campo2, campo3)<BR>    VALUES(0, id, r.mi_campo);<BR>END LOOP;<BR>END;