sql - 更新命令在嵌套的 for 循环 postgres 中不起作用

Question

我嵌套了如下所示的for循环并尝试执行更新语句，但更新命令不起作用

CREATE or REPLACE FUNCTION public.udf_unit_test_output() RETURNS SETOF text AS
$$
    DECLARE
        v_expected_proc_name        varchar(100);
        v_expected_log_severity     varchar(25);
        v_expected_log_text         varchar(8000);
        v_expected_occurence        int;
        v_actual_occurence          int;
        record_list                 record;
        distinct_unit_test_uuid     uuid;
    BEGIN
        for distinct_unit_test_uuid in
            SELECT DISTINCT unit_test_uuid FROM public.unit_test_expected_results
        loop
            for record_list in select record_id
                       from public.unit_test_expected_results
                       WHERE    unit_test_uuid  = distinct_unit_test_uuid
                loop
                SELECT  proc_name, log_severity, log_text, occurence
                FROM    public.unit_test_expected_results
                WHERE   record_id = record_list.record_id
                INTO    v_expected_proc_name,
                        v_expected_log_severity,
                        v_expected_log_text,
                        v_expected_occurence;
                SELECT  COUNT(*)
                FROM    public.unit_test_output uto
                WHERE   unit_test_uuid  = distinct_unit_test_uuid
                    AND proc_name       = v_expected_proc_name
                    AND log_severity    = v_expected_log_severity
                    AND log_text        like v_expected_log_text
                INTO    v_actual_occurence;

                UPDATE  public.unit_test_expected_results
                SET     pass_fail = (CASE WHEN v_expected_occurence = v_actual_occurence
                                     THEN 'PASS' ELSE 'FAIL'
                                     END)
                WHERE   record_id = record_list.record_id;

                RETURN QUERY SELECT is(v_expected_occurence, v_actual_occurence,
                    'Should contain '||'proc name: ' || v_expected_proc_name || ' severity type: '
                        || v_expected_log_severity || ' and log text: ' || v_expected_log_text);
            end loop;
         end loop;
         RETURN;
    END;
$$
LANGUAGE plpgsql;

它不更新任何行？

标签： sqlpostgresqlplpgsql