How do you alias and reference that alias in another part of a stored procedure in Postgresql 11?

Question

I am writing a stored procedure where I need to define a temporary row when looping through a set of values and then reference attributes of that temporary row in the update statements that follow. Here is my stored procedure:

create or replace procedure updateReputation(sentID int)
as $$ 
declare temprow peoplereviews%rowtype;
declare grade double precision = getRuleGrade(sentID);
  begin
    for temprow in select pr.reviewerid as rid, pr.rulereviewid as rri, pr.rulereview as rr, r.reputation as r
                   from peoplereviews pr inner join sentencerules sr on pr.sentenceid = sr.sentenceid
                   inner join reviewers r on pr.reviewerid = r.reviewerid
                   where pr.sentenceid = sentID
                   and sr.taggedruleid = pr.rulereviewid
    loop
      if (grade > .5) then
        if (temprow.rr = 1) then
          update reviewers
          set reputation = temprow.r + 10
          where reviewerid = temprow.rid;
        else
          update reviewers
          set reputation = temprow.r - 10
          where reviewerid = temprow.rid;
        end if;
      else
        if (temprow.rr = 0) then
          update reviewers
          set reputation = temprow.r + 10
          where reviewerid = temprow.rid;
        else
          update reviewers
          set reputation = temprow.r - 10
          where reviewerid = temprow.rid;
        end if;
    end if;
  end loop;
  end;
$$ language plpgsql;

When I call this I get the error:

ERROR:  record "temprow" has no field "rr"
CONTEXT:  SQL statement "SELECT (temprow.rr = 1)"
PL/pgSQL function updatereputation(integer) line 12 at IF
SQL state: 42703

I also tried not using aliases and writing it as temprow.pr.rulereview for example, and got this error:

missing FROM-clause entry for table "pr"
LINE 1: SELECT (temprow.pr.rulereview = 1)
                ^
QUERY:  SELECT (temprow.pr.rulereview = 1)
CONTEXT:  PL/pgSQL function updatereputation(integer) line 12 at IF
SQL state: 42P01

So I am wondering if you can use an alias in this situation and if not, what the best way is to reference the attributes of the temporary row.

Answer 1

Sidestepping your issue with the attributes of the declared row type, you don't need any of that complicated PL/PGSQL code. You can use a single SQL UPDATE command with a FROM list to join against:

CREATE OR REPLACE PROCEDURE updateReputation(sentID int)
LANGUAGE sql
AS $$
  UPDATE reviewers r
  SET reputation = reputation + (CASE WHEN 
      pr.rulereview = (getRuleGrade(sentID) > .5)::int
    THEN
      +10
    ELSE
      -10
    END)
  FROM peoplereviews pr
  INNER JOIN sentencerules sr ON (pr.sentenceid = sr.sentenceid
                                  AND pr.rulereviewid = sr.taggedruleid)
  WHERE pr.reviewerid = r.reviewerid -- this is the join
    AND pr.sentenceid = sentID;
$$;