如何基于两表列条件生成PROPERTY表的is_beyond_ul布尔字段值？

现有两张表equipment_type和PROPERTY，表结构定义如下：

CREATE TABLE IF NOT EXISTS equipment_type (
    class_code              class_code          NOT NULL,
    major_code              character(2)        NOT NULL,
    minor_code              character(2)        NOT NULL,
    estimated_useful_life   integer             NOT NULL,    -- 单位：年
    PRIMARY KEY (class_code, major_code, minor_code)
);

CREATE TABLE IF NOT EXISTS PROPERTY(
    property_number         character(16)       PRIMARY KEY,
    class_code              class_code          NOT NULL,
    major_code              character(2)        NOT NULL,
    minor_code              character(2)        NOT NULL,
    date_acquired           date                NOT NULL,               
    warranty_period         integer,
    warranty_start_date     date,
    warranty_end_date       date
        GENERATED ALWAYS AS (
            warranty_start_date + (interval '1 year' *  warranty_period)
        ) STORED,
    is_beyond_ul            boolean
        GENERATED ALWAYS AS (
            -- 待填充的条件
        ) STORED,
    FOREIGN KEY (class_code, major_code, minor_code)
        REFERENCES equipment_type (class_code, major_code, minor_code)
);

示例数据如下：

INSERT INTO equipment_type (class_code, major_code, minor_code, estimated_useful_life)
    VALUES
        ('CE', '01', '01', 10),
        ('CE', '02', '01', 10);

INSERT INTO PROPERTY (property_number, class_code, major_code, minor_code, date_acquired, warranty_period, warranty_start_date)
    VALUES
        ('10-0518IT39020042', 'CE', '01', '01', '2014-12-01', 1, '2014-12-01'),
        ('10-0518IT39020034', 'CE', '02', '01', '2015-03-15', 3, '2015-03-18');

需求：为PROPERTY表的is_beyond_ul列设置生成逻辑，当当前日期与购入日期的间隔超过对应设备类型的预计使用年限时，该列值为true，否则为false。

由于is_beyond_ul的值依赖关联表equipment_type中的estimated_useful_life字段，我们可以在生成列的表达式中使用标量子查询获取对应设备类型的预计使用年限，再通过日期计算判断是否超期。

修改后的PROPERTY表定义如下：

CREATE TABLE IF NOT EXISTS PROPERTY(
    property_number         character(16)       PRIMARY KEY,
    class_code              class_code          NOT NULL,
    major_code              character(2)        NOT NULL,
    minor_code              character(2)        NOT NULL,
    date_acquired           date                NOT NULL,               
    warranty_period         integer,
    warranty_start_date     date,
    warranty_end_date       date
        GENERATED ALWAYS AS (
            warranty_start_date + (interval '1 year' *  warranty_period)
        ) STORED,
    is_beyond_ul            boolean
        GENERATED ALWAYS AS (
            CURRENT_DATE > (date_acquired + (interval '1 year' * 
                (SELECT estimated_useful_life 
                 FROM equipment_type 
                 WHERE class_code = PROPERTY.class_code 
                   AND major_code = PROPERTY.major_code 
                   AND minor_code = PROPERTY.minor_code)))
        ) STORED,
    FOREIGN KEY (class_code, major_code, minor_code)
        REFERENCES equipment_type (class_code, major_code, minor_code)
);

逻辑说明 #

1. 标量子查询会根据当前资产的class_code、major_code、minor_code，从equipment_type表中匹配对应设备的预计使用年限；
2. 通过date_acquired + (interval '1 year' * estimated_useful_life)计算出该资产的预计报废日期；
3. 对比当前日期CURRENT_DATE是否晚于报废日期，若是则is_beyond_ul返回true，否则返回false。

验证查询 #

可以执行以下SQL验证生成列的结果：

SELECT 
    property_number, 
    date_acquired, 
    (SELECT estimated_useful_life FROM equipment_type et 
     WHERE et.class_code = p.class_code 
       AND et.major_code = p.major_code 
       AND et.minor_code = p.minor_code) AS estimated_useful_life,
    is_beyond_ul
FROM PROPERTY p;

内容的提问来源于stack exchange，提问作者pi3.14