Postgres insert trigger to update multiple columns on new row if null

Question

If an entire row of null values is inserted into my table, I am wanting to execute a trigger to change all columns the newly inserted row to the average instead of null.

I have created the trigger function:

create or replace function teammate_null_check()
returns trigger as 
$$
begin 
    if new.score is null then new.score = (select avg(score) from doubles.teammate);
    elsif new.goals is null then new.goals = (select avg(goals) from doubles.teammate);
    elsif new.assists is null then new.assists = (select avg(assists) from doubles.teammate);
    elsif new.saves is null then new.saves = (select avg(saves) from doubles.teammate);
    elsif new.shots is null then new.shots = (select avg(shots) from doubles.teammate);
    end if;
    return new;
    end;
$$ language plpgsql

And the trigger event:

create trigger teammate_trigger
before insert on doubles.teammate
for each row
execute procedure teammate_null_check()

However when I insert a null value for all columns on my table, the trigger only sets the first column (score) to the average. I've tried using ifelse and a case statement and they both only update the first column.

This is what the table looks like after insert:

score	goals	assist	saves	shots
1234	1	2	3	4
1234	null	null	null	null

How can I update all columns if the entire row is inserted null?

don't use elsif ... if you want them to be executed separately. Use if .. end if for each field — Kadet
– Kadet, Commented Jun 15, 2022 at 23:27

nbk · Accepted Answer · 2022-06-15 23:34:42Z

ELsif for the alghorithm to choose only one column, but if ypu want all columns t be checked, you need to check eac column individually

create or replace function teammate_null_check()
returns trigger as 
$$
begin 
    if new.score is null then 
        new.score = (select avg(score) from doubles.teammate); 
        end if;
    if new.goals is null then new.goals = (select avg(goals) from doubles.teammate); end if;
    if new.assists is null then new.assists = (select avg(assists) from doubles.teammate); end if;
    if new.saves is null then new.saves = (select avg(saves) from doubles.teammate); end if;
    if new.shots is null then new.shots = (select avg(shots) from doubles.teammate);
    end if;
    return new;
    end;
$$ language plpgsql

Collectives™ on Stack Overflow

Postgres insert trigger to update multiple columns on new row if null

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related