Use variable as column name



  • 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();



  • 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 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

    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:

    • https://dba.stackexchange.com/questions/105721/use-trigger-to-synchronize-columns-with-fields-in-json-column-on-insert-or-updat/105928#105928

    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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=afca2edb0f26167d1f01b2ef967f74fd

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

    • https://stackoverflow.com/a/19364694/939860



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2