postgresql 一个简单的 pg/plsql 循环示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5114336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A simple pg/plsql loop example
提问by ivanceras
We are redesigning table schema in postgres. It used to have a linker table to the two tables partnerand advertiserof the name partner_advertisersince we were assuming a many-many relationship between partner and advertiser. A change is made such that an advertisershall have only one partner, so partnerwill have a one-may relationship to advertiser.
我们正在重新设计 postgres 中的表架构。因为我们假设合作伙伴和广告商之间存在多对多关系，所以它曾经有一个指向两个表partner和advertiser名称的链接器表partner_advertiser。进行了更改，使得 anadvertiser应只有一个partner，因此partner将与 具有 one-may 关系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.
顺便说一句，我自己没有做过任何 pg/plsql。因此，如果我应该采取任何基本步骤，请提醒我。
回答by qmajor
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
通常，像这样的简单关系转换永远不需要循环。但是，如果您确实需要一个，请查看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
一个额外的注意：如果advertisingrid不是唯一的，任何转换显然都会丢失数据，因此您应该首先运行类似
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.
如果它返回任何行，您将需要手动修复它。
回答by ivanceras
This also works. Using a simple plpgsqlsnippet:
这也有效。使用一个简单的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;
回答by dalejandropc
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;
