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.

score 12 · Accepted Answer · answered Feb 25 '11 at 06:58

12

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.

answered Feb 25 '11 at 06:58

qmajor

216
3
2

Note that the update succeeds even if there are multiple tuples in partner_advertiser table for a single advertiser. – jmz Feb 25 '11 at 07:02
it works. I never thought there is a simple command like that. – ivanceras Feb 25 '11 at 07:32

score 6 · Answer 2 · edited Apr 15 '15 at 15:12

6

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;

edited Apr 15 '15 at 15:12

Gert

532
12
19

answered Feb 25 '11 at 07:33

ivanceras

1,415
3
17
28

score -2 · Answer 3 · edited Aug 09 '16 at 12:56

-2

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;

edited Aug 09 '16 at 12:56

S M

3,133
5
30
59

answered Aug 09 '16 at 11:38

dalejandropc

1

A simple pg/plsql loop example

3 Answers3