sql - 带有 case 语句的 PostgreSQL 函数

Question

我是 SQL 函数的新手，目前正在使用 PostgreSQL。我有两个主要使用的表，每个表大约有 4-5 百万行。一，我们称之为事务表，表有一个ID的一对多不同的事务，该ID可以在主表中被添加、删除或更新。另一个表称为注册表，每个 ID 都有一个记录，其中包含姓名、地址、出生日期等。我需要通过事务表并根据“原因代码”将该记录插入注册表，更新该ID在注册表中的记录，或从注册表中删除该记录。

我的计划是首先将最低 ID 的所有记录移动到“当前工作事务”表中，然后使用 case 语句查看“事务类型”字段的值并执行插入、删除或更新. 这将一直持续到所有记录都从工作事务表中消失为止。每次一个 ID 完成 case 语句并完成所有事务时，它们将从当前正在工作的事务中删除，仅在事务表中留下当前正在工作的 ID。

我一直在研究这个函数，创建函数时没有出现任何错误，但是当我执行它时，唯一发生的事情就是记录被移动到当前事务表中。案例陈述似乎没有通过。

CREATE OR REPLACE FUNCTION makeUpdates() RETURNS VOID AS $$
DECLARE
    code VARCHAR := (SELECT reasoncode FROM current_working_trans);
BEGIN
    LOOP
        INSERT INTO current_working_trans 
            SELECT * FROM test_working_trans 
            WHERE identification_number = (SELECT MIN(identification_number) FROM test_working_trans);
        DELETE FROM test_working_trans
            WHERE identification_number = (SELECT MIN(identification_number) FROM test_working_trans);

        CASE
            WHEN code IN('NEW','REACTIVATE','TRANSFER IN','REINSTATE','CHANGE IN') THEN
                    INSERT INTO registration_table
                        (sourceid, lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype, 
                         regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips, 
                         jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear, 
                         birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5)
                    SELECT identification_number, last_name, first_name, middle_name, suffix, house_number, housenumbersuffix, 
                    street_name, streettypecodename, post_direction, apt_num, city, state, zip::int, address_line_1, address_line_2, 
                    locality_code::int, localityname, precinct_code_value, precinctname, cong_code_value::int, stsenate_code_value::int, 
                    sthouse_code_value::int, gender, EXTRACT(year FROM dob), EXTRACT(month FROM dob), EXTRACT(day FROM dob), 
                    registration_date, mailing_address_line_1, mailing_address_line_2, mailing_city, mailing_state, LEFT(mailing_zip, 5)    
                    FROM current_working_trans;
                    --ADD ON CONFLICT STATEMENT FOR IF IT'S ALREADY DONE
                    DELETE FROM current_working_trans WHERE reasoncode = code;
            WHEN code IN('INACTIVE','ACTIVE CANCEL','DECLARED NON-CITIZEN','INELIGIBLE','OUT OF STATE','MENTALLY INCAPACITATED',
                  'INACTIVE CANCEL - PURGE','CHANGE OUT','FELON','REGISTRAR ERROR','TRANSFER OUT','DECEASED','INACTIVE CANCEL - OTHER',
                  'PER CHOICE') THEN
                    INSERT INTO deletions
                    SELECT * FROM registration_table
                    WHERE registration_table.sourceid = current_working_trans.identification_number;
                    DELETE FROM registration_table WHERE registration_table.sourceid = current_working_trans.identification_number;
                    DELETE FROM current_working_trans WHERE reasoncode = code;
            WHEN code = 'NAME CHANGE' THEN 
                    UPDATE registration_table
                    SET firstname = current_working_trans.first_name,
                        lastname = current_working_trans.last_name,
                        middlename = current_working_trans.middle_name,
                        namesuffix = current_working_trans.suffix
                    FROM current_working_trans
                    WHERE sourceid = identification_number; 
                    DELETE FROM current_working_trans WHERE reasoncode = code; 
            ELSE 
                INSERT INTO deletions (firstname, lastname) SELECT first_name, last_name FROM current_working_trans;
        END CASE;
        EXIT WHEN NOT FOUND;
    END LOOP; 
END;
$$
LANGUAGE plpgsql;

标签： sqldatabasepostgresqlpostgresql-10