CODEWITHSUNDEEP
postgresqltriggers
Michał
Michał

Reputation: 18

PostgreSQL trigger function update after insert

I am new at PostgreSQL triggers. Based on documentation I was trying to make trigger function, but it's not working and don't understand why.

There is a scraping which insert into my DB records of realestates. I want to make update of new inserted records based on selected conditions.

Here is my code for trigger function:

CREATE OR REPLACE FUNCTION czynsz_clear() RETURNS TRIGGER AS $body$
    BEGIN
            IF administrative_fees IS NOT NULL
                AND administrative_fees_m2 IS NULL
                AND area IS NOT NULL
                AND type_id IN (6,1)
                AND administrative_fees > 1 AND area > 1
            THEN
            UPDATE realestates_realestate SET administrative_fees_m2 = TRUNC((administrative_fees/ AREA):: INTEGER,2)
            RETURN NEW;
            END IF;
    END;
$body$ LANGUAGE plpgsql;

So the logic is this: After inserting a new record into database realestates_realestate, I want to check: Are selected fields null or some other condition, and then make an update. After creating the function, I wanted to make a trigger launch this function after inserting into the table.

What I am doing wrong? I am using HeidiSQL (10.3.0.5771) and this message appears:

ERROR: syntax error at near RETURN. Line 11: RETURN NEW;

After I deleted this RETURN NEW, there appears an error, END IF;, and the same message.

Upvotes: 0

Views: 1505

Answers (1)

user330315
user330315

Reputation:

If you want to change the row that was just inserted, don't use UPDATE assign the value to the field or the new record.

To access the column values, use the new record as well.

Something like this:

CREATE OR REPLACE FUNCTION czynsz_clear() 
  RETURNS TRIGGER 
AS $body$ 
BEGIN
  if new.administrative_fees IS NOT NULL 
     AND new.administrative_fees_m2 IS NULL 
     AND new.area IS NOT NULL
     AND new.type_id IN (6,1)
     AND new.administrative_fees > 1 
     AND new.area > 1
  THEN    
    new.administrative_fees_m2 := TRUNC((administrative_fees/ AREA)::INTEGER,2);
  END IF;

  RETURN NEW; -- this needs to be outside of the IF
END;
$body$ LANGUAGE plpgsql;

This assumes the trigger is defined as a row level trigger, e.g.

You also need to make it a BEFORE trigger

create trigger ..
  BEFORE INSERT OR UPDATE on ....
  FOR EACH ROW execute procedure czynsz_clear();

The error you got was caused by the fact that your UPDATE statement was not ended with a ;. But even after fixing that, you would have gotten errors, because the column names (in the IF part) can't be referenced like that.

And finally the trigger would have only worked in case the IF conditions were true, because otherwise the trigger would not return anything. So the return new; needs to be outside the IF statement.

Upvotes: 0

Related Questions