更新视图规则- PostgreSQL

Question

我的更新视图"fva_gpi_sbcpcba_voapcba“的规则没有正确地更新"general_product_info”和"fva_3800“表中的"product_serial_number”列。

它只更新更新规则中写入的第一个表中的"product_serial_number“，而不是同时更新这两个表。也就是说，在本例中，它将更新fva_3800表的product_serial_number，但不会更新general_product_info表的update。

更新规则：(删除过长和不重要的代码，替换为...)

CREATE RULE update_fva_fullview
AS ON UPDATE TO fva_gpi_sbcpcba_voapcba
DO INSTEAD
(UPDATE voa_pcba
SET (voa_pcba_serial_number, ..., ch4_voa_cqr_link)
= (NEW.voa_pcba_serial_number, ..., NEW.ch4_voa_cqr_link)
WHERE voa_pcba_serial_number = OLD.voa_pcba_serial_number;
UPDATE sbc_pcba
SET (sbc_pcba_serial_number, sbc_fw_revision, sbc_mac_address, sbc_test_result_link)
= (NEW.sbc_pcba_serial_number, NEW.sbc_fw_revision, NEW.sbc_mac_address, NEW.sbc_test_result_link)
WHERE sbc_pcba_serial_number = OLD.sbc_pcba_serial_number;
UPDATE fva_3800
SET (product_serial_number, status_pcba_serial_number, sbc_pcba_serial_number, voa_pcba_serial_number) 
= (NEW.product_serial_number, NEW.status_pcba_serial_number, NEW.sbc_pcba_serial_number, NEW.voa_pcba_serial_number)
WHERE product_serial_number = OLD.product_serial_number;
UPDATE general_product_info
SET (product_serial_number, part_number, innovator_product_bom, calibration_certificate, manual, 
application_sw_name, application_sw_rev, test_report_link, production_exceptions, notes)
= (NEW.product_serial_number, NEW.part_number, NEW.innovator_product_bom, NEW.calibration_certificate, NEW.manual, 
NEW.application_sw_name, NEW.application_sw_rev, NEW.test_report_link, NEW.production_exceptions, NEW.notes)
WHERE product_serial_number = OLD.product_serial_number;);

查看：(完整...对不起，这篇文章很长，但我不想引起混淆)

CREATE OR REPLACE VIEW fva_gpi_sbcpcba_voapcba AS
SELECT product_serial_numbers.product_serial_number, general_product_info.part_number, general_product_info.innovator_product_bom, 
general_product_info.calibration_certificate, general_product_info.manual, general_product_info.application_sw_name, 
general_product_info.application_sw_rev, general_product_info.test_report_link, fva_3800.status_pcba_serial_number, 
sbc_pcba.sbc_pcba_serial_number, sbc_pcba.sbc_fw_revision, sbc_pcba.sbc_mac_address, sbc_pcba.sbc_test_result_link, 
voa_pcba.voa_pcba_serial_number, voa_pcba.voa_fw_revision, voa_pcba.voa_test_result_link, voa_pcba.ch1_tap_part_number, 
voa_pcba.ch1_tap_serial_number, voa_pcba.ch1_tap_cqr_link, voa_pcba.ch1_voa_part_number, voa_pcba.ch1_voa_serial_number, 
voa_pcba.ch1_voa_cqr_link, voa_pcba.ch2_tap_part_number, voa_pcba.ch2_tap_serial_number, voa_pcba.ch2_tap_cqr_link, 
voa_pcba.ch2_voa_part_number, voa_pcba.ch2_voa_serial_number, voa_pcba.ch2_voa_cqr_link, voa_pcba.ch3_tap_part_number, 
voa_pcba.ch3_tap_serial_number, voa_pcba.ch3_tap_cqr_link, voa_pcba.ch3_voa_part_number, voa_pcba.ch3_voa_serial_number, 
voa_pcba.ch3_voa_cqr_link, voa_pcba.ch4_tap_part_number, voa_pcba.ch4_tap_serial_number, voa_pcba.ch4_tap_cqr_link, 
voa_pcba.ch4_voa_part_number, voa_pcba.ch4_voa_serial_number, voa_pcba.ch4_voa_cqr_link, general_product_info.production_exceptions, 
general_product_info.notes, general_product_info.gpi_id
FROM product_serial_numbers
INNER JOIN general_product_info ON product_serial_numbers.product_serial_number = general_product_info.product_serial_number
INNER JOIN fva_3800 ON product_serial_numbers.product_serial_number = fva_3800.product_serial_number
INNER JOIN sbc_pcba ON fva_3800.sbc_pcba_serial_number = sbc_pcba.sbc_pcba_serial_number
INNER JOIN voa_pcba ON fva_3800.voa_pcba_serial_number = voa_pcba.voa_pcba_serial_number;

product_serial_number在product_serial_numbers表中是一个pkey，在general_product_info和fva_3800表中是唯一的fkey。

编辑：

fva_3800_product_serial_number_fkey FOREIGN KEY (product_serial_number) REFERENCES product_serial_numbers(product_serial_number) ON UPDATE CASCADE ON DELETE SET NULL

fkeys的所有约束如下所示。它们都有CASCADE和SET NULL。

fva_3800表的定义如下：

CREATE TABLE FVA_3800 (
    id serial CONSTRAINT fva_3800_id_pkey PRIMARY KEY,
    product_serial_number varchar(32) UNIQUE REFERENCES Product_Serial_Numbers(product_serial_number) ON UPDATE CASCADE ON DELETE SET NULL,
    status_PCBA_serial_number varchar(32),
    SBC_PCBA_serial_number varchar(32) REFERENCES SBC_PCBA(SBC_PCBA_serial_number) ON UPDATE CASCADE ON DELETE SET NULL,
    VOA_PCBA_serial_number varchar(32) REFERENCES VOA_PCBA(VOA_PCBA_serial_number) ON UPDATE CASCADE ON DELETE SET NULL
);

Answer 1

我没有使用规则，而是使用了函数和触发器。

对于更新或删除，规则在这种情况下不能正常工作

CREATE FUNCTION update_fva_full() RETURNS TRIGGER AS $_$
BEGIN
UPDATE voa_pcba
SET (voa_pcba_serial_number, voa_fw_revision, voa_test_result_link, ch1_tap_part_number, ch1_tap_serial_number, ch1_tap_cqr_link,
ch1_voa_part_number, ch1_voa_serial_number, ch1_voa_cqr_link, ch2_tap_part_number, ch2_tap_serial_number, ch2_tap_cqr_link,
ch2_voa_part_number, ch2_voa_serial_number, ch2_voa_cqr_link, ch3_tap_part_number, ch3_tap_serial_number, ch3_tap_cqr_link,
ch3_voa_part_number, ch3_voa_serial_number, ch3_voa_cqr_link, ch4_tap_part_number, ch4_tap_serial_number, ch4_tap_cqr_link,
ch4_voa_part_number, ch4_voa_serial_number, ch4_voa_cqr_link)
= (NEW.voa_pcba_serial_number, NEW.voa_fw_revision, NEW.voa_test_result_link, NEW.ch1_tap_part_number, NEW.ch1_tap_serial_number, NEW.ch1_tap_cqr_link,
NEW.ch1_voa_part_number, NEW.ch1_voa_serial_number, NEW.ch1_voa_cqr_link, NEW.ch2_tap_part_number, NEW.ch2_tap_serial_number, NEW.ch2_tap_cqr_link,
NEW.ch2_voa_part_number, NEW.ch2_voa_serial_number, NEW.ch2_voa_cqr_link, NEW.ch3_tap_part_number, NEW.ch3_tap_serial_number, NEW.ch3_tap_cqr_link,
NEW.ch3_voa_part_number, NEW.ch3_voa_serial_number, NEW.ch3_voa_cqr_link, NEW.ch4_tap_part_number, NEW.ch4_tap_serial_number, NEW.ch4_tap_cqr_link,
NEW.ch4_voa_part_number, NEW.ch4_voa_serial_number, NEW.ch4_voa_cqr_link)
WHERE voa_pcba_serial_number = OLD.voa_pcba_serial_number;
UPDATE sbc_pcba
SET (sbc_pcba_serial_number, sbc_fw_revision, sbc_mac_address, sbc_test_result_link)
= (NEW.sbc_pcba_serial_number, NEW.sbc_fw_revision, NEW.sbc_mac_address, NEW.sbc_test_result_link)
WHERE sbc_pcba_serial_number = OLD.sbc_pcba_serial_number;
UPDATE fva_3800
SET (product_serial_number, status_pcba_serial_number, sbc_pcba_serial_number, voa_pcba_serial_number) 
= (NEW.product_serial_number, NEW.status_pcba_serial_number, NEW.sbc_pcba_serial_number, NEW.voa_pcba_serial_number)
WHERE product_serial_number = OLD.product_serial_number;
UPDATE general_product_info
SET (product_serial_number, part_number, innovator_product_bom, calibration_certificate, manual, 
application_sw_name, application_sw_rev, test_report_link, production_exceptions, notes)
= (NEW.product_serial_number, NEW.part_number, NEW.innovator_product_bom, NEW.calibration_certificate, NEW.manual, 
NEW.application_sw_name, NEW.application_sw_rev, NEW.test_report_link, NEW.production_exceptions, NEW.notes)
WHERE product_serial_number = OLD.product_serial_number;
RETURN OLD;
END $_$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_from_fva_view
INSTEAD OF UPDATE ON fva_gpi_sbcpcba_voapcba
FOR EACH ROW EXECUTE PROCEDURE update_fva_full();