postgresql 如何迭代查询结果

p1iqtdky 于 2023-03-17 发布在 PostgreSQL

关注(0)|答案(4)|浏览(218)

我正在用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 .如果我想做什么很清楚，你能告诉我正确的方法吗？

postgresql

来源：https://stackoverflow.com/questions/32668201/how-to-iterate-over-results-of-query

4条答案

按热度按时间

huwehgph1#

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

赞(0）回复(0）举报 2023-03-17

svmlkihl2#

一个函数，循环选择和使用循环项值以过滤和计算其他值，

CREATE FUNCTION "UpdateTable"() RETURNS boolean
    LANGUAGE plpgsql
AS
$$
DECLARE
    TABLE_RECORD RECORD;
    BasePrice NUMERIC;
    PlatformFee NUMERIC;
    MarketPrice NUMERIC;
    FinalAmount NUMERIC;
BEGIN
    FOR TABLE_RECORD IN SELECT * FROM "SchemaName1"."TableName1" -- can select required fields only

        LOOP
            SELECT "BasePrice", "PlatformFee" INTO BasePrice, PlatformFee
            FROM "SchemaName2"."TableName2" WHERE "UserID" = TABLE_RECORD."UserRID";

            SELECT "MarketPrice" / 100 INTO MarketPrice FROM "SchemaName3"."TableName3" WHERE "DateTime" = TABLE_RECORD."DateTime";

            FinalAmount = TABLE_RECORD."Qty" * (BasePrice + PlatformFee - MarketPrice);

            UPDATE "SchemaName1"."TableName1" SET "MarketPrice" = MarketPrice, "Amount" = CFDAmount
            WHERE "ID" = CFD_RECORD."ID"; -- can update other schema tables also
        END LOOP;

    RETURN TRUE;
END
$$;

赞(0）回复(0）举报 2023-03-17

ih99xse13#

为了将来参考，我想强调Thushara对已接受答案的评论
在Postgres@12上可以使用以下方法：

DO $$
DECLARE temprow RECORD;
BEGIN 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;
END $$

赞(0）回复(0）举报 2023-03-17

zwghvu4y4#

@Tazahi莱赫完美-谢谢！但是不要忘记END后面的分号：）

DO $$
  DECLARE temprow RECORD;
  BEGIN 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;
  END;
$$

赞(0）回复(0）举报 2023-03-17

我来回答

postgresql 如何迭代查询结果

4条答案

相关问题

热门标签

最新问答