postgresql - 用随机等级更新表

Question

我有一个函数旨在为表 simulated_records 中的特定学校分配分数，该函数基于学生人数和每个学生获得该学校特定分数的概率。我已经记下了所有概率信息，但在尝试使用正确的成绩更新表格时出现错误。我不知道是我更新不正确还是将更新放在了正确的区域。

 CREATE FUNCTION assigngrades(probs numeric[], school text, record_id bigint) RETURNS void AS $$
 DECLARE
num_grades integer ARRAY[6];
found_school school_probs%ROWTYPE;
num_students simulated_records%ROWTYPE;
random_record simulated_records%ROWTYPE;
num_students_int bigint;



BEGIN
SELECT INTO found_school * FROM school_probs WHERE school_code = record_id;
SELECT INTO num_students * FROM simulated_records WHERE school = school_name;
SELECT COUNT(*) INTO num_students_int FROM simulated_records WHERE school = school;

num_grades[1] = num_students_int*found_school.probs[1];
num_grades[2] = num_students_int*found_school.probs[1];
num_grades[3] = num_students_int*found_school.probs[1];
num_grades[4] = num_students_int*found_school.probs[1];
num_grades[5] = num_students_int*found_school.probs[1];
num_grades[6] = num_students_int*found_school.probs[1];

FOR i IN 1..num_grades[1] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
       random_record.grade = 'A';

END IF;
UPDATE simulated_records.grade = random_record.grade;
END Loop; 

FOR i IN 1..num_grades[2] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
        random_record.grade := 'A-';
END IF;
UPDATE simulated_records.grade = random_record.grade;
END Loop; 
FOR i IN 1..num_grades[3] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
        random_record.grade := 'B+';
END IF;
UPDATE simulated_records.grade = random_record.grade;
END Loop; 
FOR i IN 1..num_grades[4] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
        random_record.grade := 'B';
END IF;
UPDATE simulated_records.grade = random_record.grade;
END Loop; 
FOR i IN 1..num_grades[5] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
        random_record.grade := 'C';
END IF;
UPDATE simulated_records.grade = random_record.grade;
END Loop; 
FOR i IN 1..num_grades[6] LOOP

    SELECT INTO random_record * FROM simulated_records WHERE school = school_name ORDER BY RANDOM() LIMIT 1;
    IF random_record.grade = '-' THEN
         random_record.grade = 'D';

END IF;
UPDATE simulated_records.grade = random_record.grade;

END Loop; 

END;
$$
LANGUAGE plpgsql

老实说，我只是不确定如何正确更新它。我该怎么办？

Answer 1

您的 UPDATE 命令中需要一个 WHERE 子句，因为

UPDATE simulated_records.grade = random_record.grade;

更新表格的所有行。