PostgreSQL中实现Oracle自定义基类型继承与TREAT功能替代咨询

一、PostgreSQL中创建可继承自定义类型 #

PostgreSQL支持可继承的复合类型，虽无Oracle的NOT FINAL关键字，但可通过类型继承语法实现类似的基类型/子类型结构：

创建类型示例 #

-- 基类型：对应Oracle的person_t NOT FINAL
CREATE TYPE person_t AS (
    name VARCHAR(100),
    ssn NUMERIC
);

-- 子类型：继承自person_t，对应Oracle的employee_t UNDER person_t NOT FINAL
CREATE TYPE employee_t AS (
    department_id NUMERIC,
    salary NUMERIC
) UNDER person_t;

-- 进一步的子类型：继承自employee_t
CREATE TYPE part_time_emp_t AS (
    hourly_rate NUMERIC,
    hours_per_week NUMERIC
) UNDER employee_t;

创建存储多态类型的表 #

CREATE TABLE persons (
    p person_t
);

插入数据 #

INSERT INTO persons VALUES (('Bob', 1234)::person_t);
INSERT INTO persons VALUES (('Joe', 32456, 12, 100000)::employee_t);
INSERT INTO persons VALUES (('Tim', 5678, 13, 1000, 20)::part_time_emp_t);

二、替代Oracle TREAT函数的查询方案 #

PostgreSQL没有直接等价的TREAT函数，但可通过类型检查+显式转换实现相同逻辑：将基类型值转换为指定子类型，转换失败时返回NULL。

方案1：使用pg_typeof判断类型 #

SELECT 
    (p).name,
    CASE 
        -- 包含employee_t及其子类型part_time_emp_t
        WHEN pg_typeof(p) IN ('employee_t'::regtype, 'part_time_emp_t'::regtype)
        THEN (p::employee_t).salary
        ELSE NULL
    END AS salary
FROM persons;

方案2：使用IS OF类型检查（PostgreSQL 12+） #

更简洁的类型检查方式，自动包含子类型：

SELECT 
    (p).name,
    CASE 
        WHEN p IS OF (employee_t)
        THEN (p::employee_t).salary
        ELSE NULL
    END AS salary
FROM persons;

三、注意事项 #

PostgreSQL的复合类型继承和Oracle的对象类型继承存在差异：

• PostgreSQL复合类型仅关注数据结构，不支持Oracle对象类型的方法、构造函数等特性；
• 若需更接近Oracle对象系统的功能，可考虑使用PostgreSQL的PL/pgSQL对象类型或继承表替代复合类型，但对于查询转换场景，上述复合类型方案已足够满足需求。

内容的提问来源于stack exchange，提问作者yesj1234