如果我没理解错的话，您希望防止任何用户在表ID建立后修改它，并让一个通用函数产生异常，同时仍然允许其他更新。您可以通过修改触发器而不是函数来实现这一点。在触发器本身上指定WHEN predicate 。对于cart表，则：

create or replace trigger cart_id_guard
   before update of cart_id 
       on cart for each row
          when (old.cart_id is distinct from new.cart_id)
       execute function surrogate_id_guard('cart_id');

对于customer表，触发器变为：

create or replace trigger customer_id_guard
   before update of customer_id 
       on customer for each row
     when (old.customer_id is distinct from new.customer_id)
  execute function surrogate_id_guard('customer_id');

触发函数本身不变。（demo here）

TL; DR

我尝试了什么？撤消UPDATE特权不起作用。

# \c danissimo danissimo
You are now connected to database "danissimo" as user "danissimo".

> revoke update (customer_id) on customer from danissimo;
REVOKE
> insert into customer (customer_id) values (0);
INSERT 0 1
> update customer set customer_id = 0 where customer_id = 0;
UPDATE 1
> update customer set customer_id = -1 where customer_id = 0;
UPDATE 1

好吧，让我们派个警卫来。

create or replace function customer_id_guard() returns trigger
language plpgsql as $body$
begin
  if old.customer_id != new.customer_id then
    raise exception
      'trigger %: updating is prohibited for %',
      tg_name, 'customer_id' using
      errcode = 'restrict_violation';
  end if;
  return new;
end;
$body$;

create or replace trigger customer_id_guard
after update on customer for each row
execute function customer_id_guard();

现在让我们给他们一些工作。

> update customer set customer_id = -1 where customer_id = -1;
UPDATE 1

好吧，我没有改变数值。这个怎么样：

> update customer set customer_id = 0 where customer_id = -1;
ERROR:  trigger customer_id_guard: updating is prohibited for customer_id
CONTEXT:  PL/pgSQL function customer_id_guard() line 4 at RAISE

是的，就是这样，很好，让我们也保护cart_id，我不想复制粘贴触发器函数，所以我们试着泛化它：

create or replace function generated_id_guard() returns trigger
language plpgsql as $body$
declare
  id_col_name text := tg_argv[0];
  equal boolean;
begin
  execute format('old.%1$I = new.%1$I', id_col_name) into equal;
  if not equal then
    raise exception
      'trigger %: updating is prohibited for %',
      tg_name, id_col_name using
      errcode = 'restrict_violation';
  end if;
  return new;
end;
$body$;

create or replace trigger cart_id_guard
after update on cart for each row
execute function generated_id_guard('cart_id');

正如您可能注意到的，我将列名传递给触发器函数并生成一个表达式，然后将该表达式的结果放入equal中进行测试。

> insert into cart (cart_id) values (0);
INSERT 0 1
> update cart set cart_id = 0 where cart_id = 0;
ERROR:  syntax error at or near "old"
LINE 1: old.cart_id = new.cart_id
        ^
QUERY:  old.cart_id = new.cart_id
CONTEXT:  PL/pgSQL function generated_id_guard() line 6 at EXECUTE

嗯......他是对的，什么是悬空的old.cart_id = new.cart_id？如果我写

execute format('select old.%1$I = new.%1$I', id_col_name) into equal;

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  missing FROM-clause entry for table "old"
LINE 1: select old.cart_id = new.cart_id
               ^
QUERY:  select old.cart_id = new.cart_id
CONTEXT:  PL/pgSQL function generated_id_guard() line 6 at EXECUTE

好吧，好吧...如果我写

declare
  id_old int;
  id_new int;
begin
  execute format('select %I from old', id_col_name) into id_old;
  execute format('select %I from new', id_col_name) into id_new;
  if id_old != id_new then

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  relation "old" does not exist
LINE 1: select cart_id from old
                            ^
QUERY:  select cart_id from old
CONTEXT:  PL/pgSQL function generated_id_guard() line 7 at EXECUTE

啊哈，"老"关系并不存在...
好吧，这是最后一招：

drop table cart;
create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone
);
insert into cart (cart_id) values (0);

create or replace function surrogate_id_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$;

create or replace trigger cart_id_guard
before update of cart_id on cart for each row
execute function surrogate_id_guard('cart_id');

我只是让它在任何尝试更新cart_id时触发。

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  trigger cart_id_guard: updating is prohibited for cart_id
CONTEXT:  PL/pgSQL function surrogate_id_guard() line 3 at RAISE
> update cart set at = now() where cart_id = 0;
UPDATE 1

好了，我终于回答了我原来的问题，但是还有一个问题：如何将函数中编码的相同算法应用于该函数的args中给定的列？

我在previous research中的第一个尝试是撤销特权，正如Laurenz Albe在他的评论中指出的，我必须撤销更新整个表的特权，而不是撤销更新某个列的特权，下面是代码：

# \c danissimo danissimo
You are now connected to database "danissimo" as user "danissimo".

create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone default now()
);
insert into cart default values;

revoke update on cart from danissimo;

我现在可以更新表格吗？

> update cart set at = at - interval '1 day';
ERROR:  permission denied for table cart

好的，让我们授予更新cart_id以外的列的权限：

> grant update (at) on cart to danissimo;
> update cart set at = at - interval '1 day';
UPDATE 1

到目前为止，一切顺利。现在时间滴答作响，最终danissimo添加了另一列item_ids：

alter table cart add column item_ids int[];

danissimo现在可以更新新列了吗？请记住，他更新整个表的权限已被撤消，更新新列的权限也未被授予：

> update cart set item_ids = array[1, 3, 7 ,5];
ERROR:  permission denied for table cart

如果我给他特权呢？

> grant update (item_ids) on cart to danissimo;
> update cart set item_ids = array[1, 3, 7 ,5];
UPDATE 1

这意味着什么呢？我考虑了两种方法。一种是一旦给列赋值，就禁止更新该列。另一种是玩弄特权。在我们的项目中，我们通常在项目发展的同时添加新列。如果我坚持使用特权，那么每次添加新列时，我都必须授予更新该列的特权。另一方面，如果我用触发器保护一些列，我只需添加新列，不再麻烦。

**结论：**使用如上所示的触发器👆🏼。