Here's the problem.
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
);
I want to protect customer_id and cart_id from updating generically once they are inserted. How?
UPD: While I was writing the question I found the answer to my original question. Here it is:
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
The WHEN clause was suggested by Belayer in his answer. The full explanation is in my research. Additionally I examined the approach with playing with privileges. NOTE: Some people say that triggers like here are performance killers. They are wrong. How do you think postgres implements constraints internally? — Using implicit triggers like defined here.
What did I try? Revoking UPDATE privilege doesn't work.
# \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
Okay, let's put a guard on it.
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();
Now let's give them some work.
> update customer set customer_id = -1 where customer_id = -1;
UPDATE 1
Right, I didn't change the value. What about this:
> 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
Yeah, here it goes. Good, let's protect cart_id as well. I don't want to copy–paste trigger functions, so I let's try to generalize it:
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');
As you might notice I pass the column name to the trigger function and generate an expression and put the result of that expression into equal which then test.
> 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
Hmmm... He's right, what the dangling old.cart_id = new.cart_id? What if I write
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
Right, right... What if I write
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
Aha, «relation "old" does not exist»...
Well, here's the last resort:
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');
I just make it trigger on any attempt to update cart_id. Let's check:
> 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
Well, finally I answered my original question at this point. But another question is still arisen: How to apply the same algorithm encoded in a function to columns given in args to that function?