PostgreSQL 遍历查询结果

发布于09月19日

我正在用pgsql脚本语言创建一个函数，在这一点上我想做的是迭代查询的结果，并对每一行执行特定的操作.我当前的try 如下，其中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;

然而，我从中得到以下错误:ERROR: relation "temprow" does not exist.如果我清楚地知道我想做什么，你能告诉我正确的方法吗？

推荐答案

temprow是一个记录变量，它依次绑定到前SELECT的每个记录.

所以你应该写:

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;

这个循环可以进一步简化为一个查询:

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