Postgresql trigger not working as expected at insert-CodePudding

I have created 2 triggers in postgresql that execute the same function. One for insert and the other one for update. The update trigger seems to be working fine, but the insert trigger is not working the way I would expect it to. I have tried changing the trigger from "before insert" to "after insert" but I didn't seem to make it work.

I should be able to set a value for "name_ip" only if column "type" is of a certain value. With the code I have written, the name_ip column gets set to null, regardless of the type column. Pretty sure it has something to do with address.id = new.id in the where clause.

This is the function I am triggering

CREATE OR REPLACE FUNCTION update_name_ip_column() 
RETURNS TRIGGER AS $$
BEGIN
    new.name_ip = new.name_ip from address, "record-type" rt 
    where 
        new."type" = rt.id  and
        rt."type" = 'PTR' and
        address.id = new.id;
    RETURN new;
END;
$$ language 'plpgsql';

And these are the 2 triggers I defined

drop trigger update_address_name_ip on "address";
CREATE TRIGGER update_address_name_ip before update ON "address"  FOR EACH ROW EXECUTE PROCEDURE  update_name_ip_column();

drop trigger insert_address_name_ip on "address";
CREATE TRIGGER insert_address_name_ip before insert ON "address"  FOR EACH row execute function update_name_ip_column();

CodePudding user response：

As I thought, the problem was using "before insert" in the where clause instead of "after insert", so everything was set to null regardless of input.

So now I'm doing an update after i do an insert, setting to null everything that doesn't contain 'PTR' in type

--insert  function
CREATE OR REPLACE FUNCTION insert_name_ip_column() 
RETURNS TRIGGER AS $$
begin
    update public.address 
    set name_ip = null
    from "record-type" rt 
    where 
        public.address."type" = rt.id  and not
        rt."type" = 'PTR' and
        public.address.id = new.id;

    --debug Table
    INSERT INTO public.test_table
        (id, name_ip, "type")
        VALUES(new.id, new.name_ip, new.type);

    RETURN new;
END;
$$ language 'plpgsql';

drop trigger insert_address_name_ip on "address";
CREATE TRIGGER insert_address_name_ip after insert ON "address"  FOR EACH row execute function insert_name_ip_column();

CodePudding user response：

You can do is as before doing something like:

CREATE OR REPLACE FUNCTION update_name_ip_column() 
RETURNS TRIGGER AS $$
DECLARE
    ptr_val varchar;
BEGIN
    select 
       into ptr_val rt."type" 
    from 
       "record-type" rt 
    where rt.id = new."type";

    if ptr_val != 'PTR'
        new.name_ip = NULL;

    --debug Table
    INSERT INTO public.test_table
        (id, name_ip, "type")
        VALUES(new.id, new.name_ip, new.type);
    RETURN new;
END;
$$ language 'plpgsql';