Use variable as column name

Question

I'm trying to create trigger in PostgreSQL 14:

 create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$
    declare
        equipment_id int := new.player_equipment_armor['armor_id'];
        equipment_stats varchar[] := array(select jsonb_object_keys(armor_stats['stats']) from equipments_armor where armor_id = equipment_id);
        equipment_stat varchar;
begin
    raise notice '%', equipment_id;
    foreach equipment_stat in array equipment_stats loop
            if old.player_equipment_armor['stats'][equipment_stat] is not null then
                 update players set equipment_stat = equipment_stat - old.player_equipment_armor['stats'][equipment_stat] + new.player_equipment_armor['stats'][equipment_stat] where player_id = new.player_id;
            end if;
    end loop;
    return new;
END;

$add_stats_to_player$ language plpgsql;
create trigger add_stats_to_player after insert or update of player_equipment_armor on players
    for each row WHEN (pg_trigger_depth() < 1) execute function add_stats_to_player();

Can I make a column name from the variable equipment_stat, or is that a bad idea?

update Here's how I did it

create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$
    declare
        equipment_id int := new.player_equipment_armor['armor_id'];
        equipment_stats varchar[] := array(select jsonb_object_keys(armor_stats['stats']) from equipments_armor where armor_id = equipment_id);
        equipment_stat varchar;
    begin
        if (TG_OP = 'UPDATE') then
            foreach equipment_stat in array equipment_stats loop
                    if old.player_equipment_armor['stats'][equipment_stat] is not null then
                       execute 'update players set ' || equipment_stat || ' = ' || equipment_stat || ' - ' || old.player_equipment_armor['stats'][equipment_stat] || '+' || new.player_equipment_armor['stats'][equipment_stat] || ' where player_id = ' || new.player_id;
                    else
                       execute 'update players set ' || equipment_stat || ' = ' || new.player_equipment_armor['stats'][equipment_stat] || ' where player_id = ' || new.player_id;
                    end if;
            end loop;
        elseif  (TG_OP = 'INSERT') then
            foreach equipment_stat in array equipment_stats loop
                execute 'update players set ' || equipment_stat || ' = ' || new.player_equipment_armor['stats'][equipment_stat] || ' where player_id = ' || new.player_id;
            end loop;
        end if;
        return new;
    end;
$add_stats_to_player$ language plpgsql;
create trigger add_stats_to_player after insert or update of player_equipment_armor on players
    for each row WHEN (pg_trigger_depth() < 1) execute procedure add_stats_to_player();

Answer 1

I can't let you do this. :)

Don't repeatedly UPDATE the triggering row. That's hugely inefficient and error-prone.

Use a BEFORE trigger instead, where you can simply assign new values to columns of the triggering row before writing it to the table.

Only covering the INSERT case. Extend to UPDATE accordingly.

Simplistic function with loop

CREATE OR REPLACE FUNCTION ins_bef_player()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   equipment_stat text;
BEGIN
   FOR equipment_stat IN
      SELECT jsonb_object_keys(armor_stats['stats'])
      FROM   equipments_armor
      WHERE  armor_id = (NEW.player_equipment_armor->>'armor_id')::int
   LOOP
      NEW := jsonb_populate_record(NEW, jsonb_build_object(equipment_stat, NEW.player_equipment_armor->'stats'->>equipment_stat));
   END LOOP;
RETURN NEW;
END
$func$;
CREATE TRIGGER add_stats_to_player
BEFORE INSERT ON players
FOR EACH ROW
EXECUTE FUNCTION ins_bef_player();

Much better than expensive multiple UPDATEs. But still inefficient. You don't need the loop with multiple assignment at all.

Much smarter jsonb_populate_record()

At best, one straight invocation of jsonb_populate_record() is all you need:

CREATE OR REPLACE FUNCTION ins_bef_player()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW, NEW.player_equipment_armor['stats']);  -- that's all !
   RETURN NEW;
END
$func$;

See:

• Use trigger to synchronize columns with fields in json column on insert or update

Additional keys in player_equipment_armor['stats'] that have no matching field in NEW are discarded automatically. And additional fields in NEW that have no mo match in player_equipment_armor['stats'] are kept as is.

If armor_stats['stats'] needs to be applied to remove keys, you can do that, too:

CREATE OR REPLACE FUNCTION ins_bef_player()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW
          , NEW.player_equipment_armor['stats']
          - ARRAY(SELECT jsonb_object_keys(NEW.player_equipment_armor['stats'])
                  EXCEPT ALL
                  SELECT jsonb_object_keys(armor_stats['stats'])
                  FROM   equipments_armor e
                  WHERE  e.armor_id = (NEW.player_equipment_armor->>'armor_id')::int)
          );
   RETURN NEW;
END
$func$;

db<>fiddle here

This removes all keys that are not present armor_stats['stats'] before calling jsonb_populate_record(). See:

• Select rows which are not present in other table