1

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();
7
  • 2
    Please provide a complete function definition, not just the rump that is invalid without the header. And always your version of Postgres. Also show the trigger definition. And clarify your question, please. "Can I make a column name from the 'equipment_stat' variable" ... do you want to use the value of equipment_stat as column name in the UPDATE command? There may be a simple pure SQL solution for this. Better yet, you should probably work with a normalized table design. May 7, 2022 at 16:06
  • @ErwinBrandstetter,Yes I want to use the value of the equipment_stat variable, and I couldn't find a way to do that. If you can do it I would be very grateful.
    – Sevas
    May 7, 2022 at 19:35
  • 1
    You would need dynamic SQL with EXECUTE. But I am hesitant to even show a solution because your approach is backwards in multiple ways. You shouldn't UPDATE the same row repeatedly. Much less the triggering row itself. And it makes no sense to RETURN NEW; in an AFTER trigger. You probably should use a BEFORE trigger and just assign new values to each NEW.column. Your IF condition also looks suspicious. Guess that's there to shoe in both INSERT and UPDATE case into the same trigger. Start by splitting both cases to separate triggers ... May 7, 2022 at 20:58
  • @ErwinBrandstetter, Thanks for the explanation and guidance.
    – Sevas
    May 8, 2022 at 10:03

1 Answer 1

0

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.