你能引用postgresql中存储过程中插入语句中临时行上的聚合函数吗

Question

我正在编写一个postgres存储过程，该过程循环通过select语句返回的行。对于它循环通过的每一行，它都会将select语句中的值插入到一个新表中。我需要在第二个表中插入的值之一是列的平均值。但是，当我调用存储过程时，我会得到一个错误，即临时行没有用于我正在求平均值的实际列的属性。请参阅下面的存储过程和错误。

存储过程：

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:  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

基本上，我想知道是否可以在insert语句中使用聚合函数，如果不能，是否有其他方法

Answer 1

您不需要为此使用缓慢且低效的循环：

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

---

要回答最初的问题：您需要为聚合提供一个适当的别名：

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;