Can you reference an aggregate function on a temporary row in an insert statement within a stored procedure in postgresql?

Question

I am writing a postgres stored procedure that loops through the rows returned from a select statement. For each row it loops through, it inserts values from that select statement into a new table. One of the values I need to insert into the second table is the average of a column. However, when I call the stored procedure, I get an error that the temporary row has no attribute for the actual column that I am averaging. See stored procedure and error below.

Stored Procedure:

create or replace procedure sendToDataset(sentence int)
as $$
declare temprow peoplereviews%rowtype;
BEGIN

  FOR temprow IN
      select rulereviewid, avg(rulereview)
      from peoplereviews 
      where sentenceid = sentence
      group by rulereviewid
  loop
      insert into TrainingDataSet(sentenceId, sentence, ruleCorrectId, ruleCorrect, dateAdded)
          values(sentence, getSentenceFromID(sentence), tempRow.rulereviewid, tempRow.avg(rulereview), current_timestamp);
    END LOOP;

END
$$ 
LANGUAGE plpgsql;

Error:

ERROR:  column "rulereview" does not exist
LINE 2: ...omID(sentence), tempRow.rulereviewid, tempRow.avg(rulereview...
                                                             ^
QUERY:  insert into TrainingDataSet(sentenceId, sentence, ruleCorrectId, ruleCorrect, dateAdded)
          values(sentence, getSentenceFromID(sentence), tempRow.rulereviewid, tempRow.avg(rulereview), current_timestamp)
CONTEXT:  PL/pgSQL function sendtodataset(integer) line 11 at SQL statement
SQL state: 42703

Basically, I am wondering if it's possible to use that aggregate function in the insert statement or not and if not, if there is another way around it.

Answer 1

you don't need to use a slow and inefficient loop for this:

 insert into TrainingDataSet(sentenceId, sentence, ruleCorrectId, ruleCorrect, dateAdded)
 select getSentenceId(sentence), sentence, rulereviewid, avg(rulereview), current_timestamp
 from peoplereviews 
 where sentenceid = sentence
 group by rulereviewid

---

To answer the original question: you need to provide a proper alias for the aggregate:

  FOR temprow IN
      select rulereviewid, avg(rulereview) as avg_views
      from peoplereviews 
      where sentenceid = sentence
      group by rulereviewid
  loop
      insert into TrainingDataSet(sentenceId, sentence, ruleCorrectId, ruleCorrect, dateAdded)
      values(sentence, getSentenceFromID(sentence), tempRow.rulereviewid, 
             tempRow.avg_views, current_timestamp);
  END LOOP;