Simple pg / plsql loop example

Question

We reverse engineer the table schema in postgres. Previously, the linker table was used for the partner and advertiser tables of the partner_advertiser table, since we were accepting many, many relationships between the partner and the advertiser. The change is made in such a way that the advertiser will have only one partner , so the partner will have a one-can relationship with the advertiser .

How can I make changes without losing previous information? Linker table data should be used to populate the display of the new schema. Here is my source 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 

By the way, I myself did not do pg / plsql. Therefore, if there are any basic steps that I must take, please give me heads-up.

Answer 1

You can use:

 UPDATE advertiser a SET partnerid = r.partnerid FROM partner_advertiser r WHERE a.id = r.advertiserid 

As a rule, simple relational transformations like these will never need a loop. If you really need to, check out http://www.postgresql.org/docs/9.0/interactive/plpgsql-control-structures.html

One more note: any conversion will obviously lose data if the advertiser is not yet unique, so first you need to run something like

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

If any rows are returned by this, you will want to fix it manually.

Answer 2

This also works. Using a simple plpgsql :

 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; 

Answer 3

 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;