PostgreSQL - 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?

Answer 1

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 

Answer 2

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