如何通用地设置PostgreSQL列不可修改？

已创建以下两张表：

create table customer (
  customer_id int generated by default as identity (start with 100) primary key
);
create table cart (
  cart_id int generated by default as identity (start with 100) primary key
);

需要通用地保护customer_id和cart_id列，确保这些列在插入数据后无法被修改，该如何实现？

可以通过创建PL/pgSQL触发器函数实现列的不可修改限制，以下是完整实现方案（以cart表为例，customer表可复用相同逻辑）：

1. 表结构调整（可选，用于演示） #

先为cart表新增几个非保护列，方便验证效果：

create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  name text not null,
  at timestamp with time zone
);

2. 创建通用触发器函数 #

这个函数可以复用给需要保护列的任意表：

create or replace function table_update_guard() returns trigger
language plpgsql immutable parallel safe cost 1 as $body$
begin
  raise exception
    'trigger %: updating is prohibited for %',
    tg_name, tg_argv[0]
    using errcode = 'restrict_violation';
  return null;
end;
$body$;

3. 创建针对目标表的触发器 #

为cart表创建触发器，指定要保护的cart_id和name列：

create or replace trigger cart_update_guard
before update of cart_id, name on cart for each row
-- 可选WHEN子句：仅当目标列实际被修改时才触发触发器
when (
     old.cart_id is distinct from new.cart_id
  or old.name    is distinct from new.name
)
execute function table_update_guard('cart_id, name');

4. 验证效果 #

执行以下SQL测试：

-- 插入数据，正常执行
insert into cart (cart_id, name) values (0, 'prado');
-- 输出：INSERT 0 1

-- 尝试修改受保护的cart_id，触发错误
update cart set cart_id = -1 where cart_id = 0;
-- 输出：ERROR:  trigger cart_update_guard: updating is prohibited for cart_id, name
-- CONTEXT:  PL/pgSQL function table_update_guard() line 3 at RAISE

-- 尝试修改受保护的name，触发错误
update cart set name = 'nasa' where cart_id = 0;
-- 输出：ERROR:  trigger cart_update_guard: updating is prohibited for cart_id, name
-- CONTEXT:  PL/pgSQL function table_update_guard() line 3 at RAISE

-- 修改未受保护的at列，正常执行
update cart set at = now() where cart_id = 0;
-- 输出：UPDATE 1

补充说明 #

• 示例中的WHEN子句由Belayer提出，它能减少不必要的触发器执行，仅当目标列的实际值发生变化时才触发限制逻辑。
• 除了触发器，还可以通过权限控制实现：撤销用户对目标列的UPDATE权限，例如执行REVOKE UPDATE (customer_id) ON customer FROM 目标用户;。
• 这类触发器对性能影响可以忽略，PostgreSQL内部的很多约束也是通过类似的隐式触发器实现的。

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