SQL PostgreSQL - 迭代查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32668201/
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
PostgreSQL - Iterate over results of query
提问by Noob Doob
I am creating a function in pgsql script language, and what I want to do in this point is iterate over the results of a query and for each row do something specific. My current try is the following, where temprowis declared as temprow user_data.users%rowtype. The code in question is the following:
我正在用 pgsql 脚本语言创建一个函数，在这一点上我想要做的是迭代查询的结果，并为每一行做一些特定的事情。我目前的尝试如下，其中temprow声明为temprow user_data.users%rowtype. 有问题的代码如下：
FOR temprow IN
SELECT * FROM user_data.users ORDER BY user_seasonpts DESC LIMIT 10
LOOP
SELECT user_id,user_seasonpts INTO player_idd,season_ptss FROM temprow;
INSERT INTO user_data.leaderboards (season_num,player_id,season_pts) VALUES (old_seasonnum,player_idd,season_ptss);
END LOOP;
However I get the following error from this: ERROR: relation "temprow" does not exist. If it's clear what I want to be done, could you point to me the right way to do it?
不过，我从中得到了以下错误：ERROR: relation "temprow" does not exist。如果很清楚我想要做什么，你能指出我正确的方法吗？
回答by Renzo
temprowis a record variable which is bound in turn to each record of the first SELECT.
temprow是一个记录变量，它依次绑定到第一个SELECT.
So you should write:
所以你应该写：
FOR temprow IN
SELECT * FROM user_data.users ORDER BY user_seasonpts DESC LIMIT 10
LOOP
INSERT INTO user_data.leaderboards (season_num,player_id,season_pts) VALUES (old_seasonnum,temprow.userd_id,temprow.season_ptss);
END LOOP;
This loop could be further simplified as a single query:
这个循环可以进一步简化为单个查询：
INSERT INTO user_data.leaderboards (season_num,player_id,season_pts)
SELECT old_seasonnum,player_idd,season_ptss FROM user_data.users ORDER BY user_seasonpts DESC LIMIT 10
