PostgreSQL - 迭代查询结果

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 temprow is 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?

但是我从这里得到以下错误:错误:关系“temprow”不存在。如果我明白我想要做什么,你能指出我正确的方法吗?

1 个解决方案

#1

temprow is 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

#1