CODEWITHSUNDEEP
sqlpostgresqltriggers
McCdrizzle
McCdrizzle

Reputation: 1

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

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?

Upvotes: 0

Views: 760

Answers (1)

nbk
nbk

Reputation: 49393

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

Upvotes: 0

Related Questions