CODEWITHSUNDEEP
postgresqlplpgsql
gxg
gxg

Reputation: 120

plpgsql query - how can i combine if , then with update?

I have the following query:

create or replace function test_function()
    returns trigger as $body$
begin
    if (tg_table_name = 'mytable' and tg_op='insert') then
        INSERT into another_table(id, name) values(new.id, new.name);
        return new;
    else if (tg_table_name = 'mytable' and tg_op='update') then
        INSERT into another_table(id, name) values(new.id, new.name);
        return new;
    end if;
    return null;
end;
$body$ language plpsql;

EDIT: I need to do a :

update table mytable set name = 'test';

EDIT2: this didn't work, the value2 was not updated to null;

BEGIN   
IF (TG_OP = 'INSERT' AND TG_TABLE_NAME='tableA') THEN
    INSERT INTO tableB(
            columnA,
            columnB)
        VALUES(
            new.value1,
            new.value2);
new.value2 := null;
RETURN NEW;
ELSEIF (TG_OP = 'UPDATE' AND 
TG_TABLE_NAME='tableA') THEN
    INSERT INTO tableB(
            columnA,
            columnB)
        VALUES(
            new.value1,
            new.value2);
new.value2 := null;
RETURN NEW;
END IF;
RETURN null;
END;

EDIT:4

create trigger add_notif_trigger after insert or update on 
 mytable for each row when (new.name > 100) execute 
 procedure function test_function();

I need to do an update on either conditions. I can't figure it out how to do it.

Kind Regards,

Upvotes: 0

Views: 67

Answers (2)

imamalis
imamalis

Reputation: 65

IF THEN IF new.value = true THEN END IF;

END IF;

be careful, when you use the update(TG_OP ='UPDATE') you have old and new value, during the insert you have only new value

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

If you want to modify the column values of base table, you don't need another update, you just assign the NEW.column_name

if
..

NEW.name := 'test';

else if
..

NEW.name := 'test';

Upvotes: 1

Related Questions