A simple pg/plsql loop example

Question

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.

Answer 1

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.

Answer 2

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;