sql - PostgreSQL-遍历查询结果

我正在用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;

但是我从中得到以下错误:ERROR: relation "temprow" does not exist。如果很清楚我想做什么，您能为我指出正确的做法吗？

最佳答案

temprow是一个记录变量，它依次绑定(bind)到第一个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