I try to automatize some calculations on tables in my database. I try to perform some UPDATE on rows that are newly inserted in a table, but I newer used NEW or OLD statements before. I tried writing the code that updates happen on new values by assigning NEW.[tablename], but it wont work. Isn't there any statement in the beginning of the trigger function to specify running the function only on new values, I cannot find useful information about this.
CREATE OR REPLACE FUNCTION cost_estimation()
RETURNS TRIGGER AS
$func$
DECLARE
a INTEGER := 3;
BEGIN
UPDATE NEW.cost_table
SET column4 = a;
UPDATE NEW.cost_table
SET column 5 = column4 - column2;
[...]
RETURN NEW;
END
$func$ language plpgsql
UPDATE:
Thank you for the answers so far.
My original code is written based on the update structure, and needs to be rewritten when omitting UPDATE. I should give a better example of my situation. Easy spoken: I have a table (T1) which will be filled with data from another table (T2).
After data is inserted in T1 from T2 I want to run calculations on the new values inside of T1.(The code includes PostGIS functionalities):
CREATE OR REPLACE FUNCTION cost_estimation()
RETURNS TRIGGER AS
$func$
BEGIN
NEW.column6 = column2 FROM external_table WHERE
St_Intersects(NEW.geom, external_table.geom) LIMIT1;
NEW.column8 = CASE
WHEN st_intersects(NEW.geom, external_table2.geom) then 'intersects'
WHEN (NEW.column9 = 'K' and NEW.column10 <= 6) then 'somethingelse'
ELSE 'nothing'
END
FROM external_table2;
[...]
RETURN NEW;
END
$func$ language plpgsql
CREATE TRIGGER table_calculation_on_new
BEFORE INSERT OR UPDATE ON cost_estimation
FOR EACH ROW EXECUTE PROCEDURE road_coast_estimation();
After inserting values in my table no calculations will be performed.
UPDATE2: I checked my tables again and detected that another trigger was blocking the table operation. The code in the lower half is working fine now, thanks to @a_horse_with_no_name.
NEW and OLD aren't "statements", those are records that represent the modified rows from the DML statement that fired the trigger.
Assuming the trigger is defined on cost_table you can simply change the fields in the NEW record. No need to UPDATE anything:
CREATE OR REPLACE FUNCTION cost_estimation()
RETURNS TRIGGER AS
$func$
DECLARE
a INTEGER := 3;
BEGIN
new.column4 := a;
new.column5 := new.column4 - new.column2;
return new;
END;
$func$ language plpgsql
For this to work the trigger needs to be defined as a BEFORE trigger:
create trigger cost_table_trigger BEFORE insert or update on cost_table for each row execute procedure cost_estimation();