How to iterate over results of query

Question

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:

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?

The right way to do it would be: "dont't iterate". What you seem to do is perfectly feasable using plain SQL : `insert into leaderboards(a,b,c) select x,y,z from users;` — wildplasser, Sep 19 '15 at 12:40
As I noticed in the answer, `old_seasonnum` is not selected at this point from `user_data.users`, but in a previous point. — Noob Doob, Sep 19 '15 at 13:14
It is allowed to use a constant (or a variable, in plpgsql or in prepared statements) where an expression is needed: `insert into foo(a,b,c) select 42, y, z from bar;` Note: in your code `old_seasonnum` is not even defined. — wildplasser, Sep 19 '15 at 13:22
I just put here the part of the code I had the problem at, it is defined in previous sectors in the actual code. If you feel it's necessary, I will put the whole code here. — Noob Doob, Sep 19 '15 at 14:30

Renzo · Accepted Answer · 2015-09-19T12:43:22.417

122

temprow is a record variable which is bound in turn to each record of the first 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

edited Sep 19 '15 at 12:43

answered Sep 19 '15 at 12:38

Renzo

26,848
5
49
61

Is `FOR`/`temprow` a cursor? Does it do any locking? – Ruslan Sep 19 '15 at 12:41
2

Yes it is a cursor, but no locking is done on the `users` table – Renzo Sep 19 '15 at 12:42
The problem with the simplified single query is, `old_seasonnum` is not selected from `user_data.users` table, but from a previous select and will be used at this point. – Noob Doob Sep 19 '15 at 13:13
3

Is this a local variable of the procedure? Then you can use it as `SELECT old_seasonnum AS season_num ...` – Renzo Sep 19 '15 at 13:18
23

To someone who wonders, DECLARE temprow RECORD; – Thushara Buddhika Dec 03 '20 at 08:34
Is the ORDER BY user_seasonpts necessary? – ennth Jul 28 '21 at 05:27
@ennth, yes it is necessary because of the `LIMIT` clause, since the statement inserts only the first 10 records according to the specified order. – Renzo Jul 28 '21 at 09:06

score 15 · Answer 2 · edited Apr 25 '23 at 15:38

For future reference, I want to emphasise Thushara comment on the accepted answer

On Postgres@12 the following would work:

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; $$

score 14 · Answer 3 · answered Dec 04 '20 at 07:11

A function that loop through the select and use loop item values to filter and calculate other values,

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
$$;

score 0 · Answer 4 · answered Mar 17 '23 at 08:49

@Tazahi Leh perfect - thank you! But don't forget a semicolon after 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;
$$

How to iterate over results of query

4 Answers4

Linked