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?
Advertisement
Answer
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