如何修正PostgreSQL中从关联表生成自动计算列的SQL语句

原SQL脚本存在语法错误与PostgreSQL生成列的规则限制，以下是具体修正及可行替代方案：

1. 原脚本的语法错误（修正后仍无法正常运行） #

原脚本的两处语法问题：

• SQL中相等判断需用=而非==
• 子查询缺少闭合的右括号

修正语法后的脚本：

ALTER TABLE IF EXISTS public.postavka 
ADD COLUMN total_price double precision 
GENERATED ALWAYS AS (
    postavka.count * (SELECT price FROM detail WHERE postavka.detail = detail.id)
) STORED;

但执行此脚本仍会报错——PostgreSQL的生成列（GENERATED ALWAYS AS ... STORED）仅能依赖当前表的列，不允许跨表引用其他字段，且其他表的detail.price更新时，生成列无法自动同步。

2. 可行的替代方案 #

方案一：创建视图（推荐，无需维护物理列） #

若不需要物理存储该字段，可创建视图实时计算结果：

CREATE OR REPLACE VIEW public.postavka_with_total AS
SELECT 
    p.*,
    p.count * d.price AS total_price
FROM public.postavka p
JOIN public.detail d ON p.detail = d.id;

后续直接查询该视图即可获取包含total_price的完整数据。

方案二：用触发器维护物理列 #

若必须在postavka表中存储total_price，可通过触发器实现自动更新：

步骤1：添加普通列

ALTER TABLE IF EXISTS public.postavka 
ADD COLUMN total_price double precision;

步骤2：创建计算更新函数

CREATE OR REPLACE FUNCTION update_postavka_total_price()
RETURNS TRIGGER AS $$
BEGIN
    SELECT NEW.count * d.price INTO NEW.total_price
    FROM public.detail d
    WHERE d.id = NEW.detail;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤3：创建触发器（处理插入/更新场景）

-- 插入数据时自动计算total_price
CREATE TRIGGER trigger_postavka_insert
BEFORE INSERT ON public.postavka
FOR EACH ROW EXECUTE FUNCTION update_postavka_total_price();

-- 更新count或detail字段时重新计算
CREATE TRIGGER trigger_postavka_update
BEFORE UPDATE OF count, detail ON public.postavka
FOR EACH ROW EXECUTE FUNCTION update_postavka_total_price();

步骤4（可选）：同步detail表价格变化

若detail.price更新时需同步修改postavka的total_price，添加以下触发器：

CREATE OR REPLACE FUNCTION sync_postavka_total_on_detail_price_change()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE public.postavka p
    SET total_price = p.count * NEW.price
    WHERE p.detail = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_detail_price_update
AFTER UPDATE OF price ON public.detail
FOR EACH ROW EXECUTE FUNCTION sync_postavka_total_on_detail_price_change();

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