问题在这里
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从更新一般一旦它们被插入。如何?乌利希期刊指南:当我在写问题的时候，我找到了我原来问题的答案。
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
);
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$;
create or replace trigger cart_update_guard
before update of cart_id, name on cart for each row
-- NOTE: the WHEN clause below is optional
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');
> insert into cart (cart_id, name) values (0, 'prado');
INSERT 0 1
> 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
> 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
> update cart set at = now() where cart_id = 0;
UPDATE 1
WHEN条款是Belayer在回答中提出的。完整的解释在我的研究中。此外，我还研究了使用特权玩游戏的方法。注意:有人说像这样的触发器是性能杀手。他们错了。你认为postgres内部是如何实现约束的?-使用像这里定义的隐式触发器。
如果我理解正确，您希望防止任何用户修改表id，一旦它建立，并有一个泛型函数产生异常，同时仍然允许其他更新。您可以通过修改触发器而不是修改函数来实现这一点。在触发器本身上指定WHEN谓词。对于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');
触发函数本身不会改变。(演示)
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
在我之前的研究中，第一次尝试是撤销特权。正如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
这些都是什么意思?我考虑了两种方法。一种方法是，一旦给了列一个值，就禁止对列进行更新。另一种是玩弄特权。在我们的项目中，随着项目的发展，我们通常会添加新的列。如果我坚持使用特权，我必须在每次添加新列时授予更新新列的特权。另一方面，如果我用触发器保护一些列，我只需要添加新列就可以了。
结论:使用如上所示的触发器👆🏼。
我能够使用可重用的函数实现最初的触发器想法。
CREATE OR REPLACE FUNCTION forbid_column_change() RETURNS TRIGGER
LANGUAGE plpgsql AS $body$
DECLARE
is_equal BOOL;
id_col_name TEXT := TG_ARGV[0];
BEGIN
EXECUTE FORMAT('
SELECT $1.%1$I IS NOT DISTINCT FROM $2.%1$I',
id_col_name
) INTO is_equal USING OLD, NEW;
IF NOT is_equal THEN
RAISE EXCEPTION
'trigger %: updating is forbidden for %.%.%',
TG_NAME, TG_TABLE_SCHEMA, TG_TABLE_NAME, id_col_name
USING
ERRCODE = 'restrict_violation';
END IF;
RETURN NEW;
END;
$body$;
CREATE TRIGGER column_guard
BEFORE UPDATE OF col_name ON table
FOR EACH ROW
EXECUTE FUNCTION forbid_column_change('col_name');
重要的是，函数定义时必须不带参数，函数触发的参数会转到TG_ARGV。