CODEWITHSUNDEEP
postgresql
Eva Rivarola
Eva Rivarola

Reputation: 31

Alter PostreSQL column into a GENERATED ALWAYS column

I have an already made table:

cotizacion(idCot(PK), unit_price,unit_price_taxes)

I need to convert unit_price_taxes into a generated column that is equal to unit_price*1.16. The issue is I can't find the alter table statement which will give me this. Dropping table and creating it again is not an option as this table is already deeply linked with the rest of the database and reinserting all records is not an option at this point.

I tried the following:

 ALTER TABLE cotizacion
  alter column unit_price_taxes set
    GENERATED ALWAYS AS (unit_price*1.16) STORED;

But it's not working. Does anybody know how to get this done or if it's even possible? I would like to avoid creating a new column.

Thanks!

**EDIT: I also tried the following trigger implementation:

CREATE OR REPLACE FUNCTION calculate_price_taxes()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare pu money;
begin
    select unit_price from cotizacion into pu
    where idCot = new."idCot";
    
    update  cotizacion
    set unit_price_taxes = pu * (1.16)
    where idCot = new."idCot";
    return new;
end;
$function$
;

And the trigger delcaration:

Create or replace trigger price_taxes
after update on cotizacion
for each row
execute procedure
calculate_price_taxes()

Upvotes: 2

Views: 2339

Answers (1)

user330315
user330315

Reputation:

The most probable reason for your trigger to go into an infinite recursion is that you are running an UPDATE statement inside the trigger - which is the wrong thing to do. Create a before trigger and assign the calculated value to the new record:

create trigger update_tax()
  returns trigger
as
$$
begin
  new.unit_price_taxes := unit_price * 1.16;
  return new;
end;
$$
language plpgsql;

create trigger update_tax_trigger()
  before update or insert on cotizacion
  for each row execute procedure update_tax();

The only way to "convert" that column to a generated one, is to drop it and add it again:

alter table cotizacion
  drop unit_price_taxes;

alter table cotizacion
  add unit_price_taxes numeric generated always as (unit_price*1.16) stored;

Note that this will rewrite the entire table which will block access to it. Adding the trigger will be less invasive.

Upvotes: 1

Related Questions