postgres: How to generically make a column immutable?-CodePudding

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 order_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,
  at timestamp with time zone
);

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');

> insert into cart (cart_id) values (0);
INSERT 0 1
> 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

The full explanation at the bottom of the further text.

But another question stays still arisen: How to make such a trigger that apply the same algorithm encoded in a function to column names given in args to that function?

UPD: Please, can you help me to ask the right question?
Maybe «How to generify a trigger function over column names?»

Thank you!

CodePudding user response：

TL;DR

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 order_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?

CodePudding user response：

If I understand correctly you want to prevent any user from modifying the the table id once it is established and to have a generic function produce the exception, while still allowing other updates. You can accomplish this this be modifying the trigger rather than the function. Specify the WHEN predicate on the trigger itself. For the cart table then:

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');

The for the customer table the trigger becomes:

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');

The trigger function itself does not change. (demo here)