A simple pg/plsql loop example

We are redesigning table schema in postgres. It used to have a linker table to the two tables partner and advertiser of the name partner_advertiser since we were assuming a many-many relationship between partner and advertiser. A change is made such that an advertiser shall have only one partner, so partner will have a one-may relationship to advertiser.

How do I make the change without losing previous information? The linker table data must be used to fill in the mapping of the new schema design. Here is my initial code:

BEGIN

FOR r IN SELECT partnerid, advertiserid from partner_advertiser
    LOOP
    NEXT r;
    UPDATE advertiser SET partnerid = r.partnerid WHERE id = r.advertiserid 
    END LOOP;

END

BTW I haven't done any pg/plsql myself. So if there are any basic steps I should make please give me a heads-up.

You can use:

UPDATE advertiser a SET partnerid = r.partnerid
FROM partner_advertiser r
WHERE 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_advertiser
GROUP 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;