(postgresql 9.2) I'v 2 tables
Table 1 (extinguisher)
extinguisher_id (serial) type_designation_extinguisher_type(text) manufacturing_date (date) life_date(date)
Table 2 (extinguisher_designation)
extinguihser_type(serial) extinguisher_designation(text) lifespan(int)
lifespan can take values like: 10 or 20 and it represents years
My objective is to do a trigger that will automatically populate table1.life_date based on the extinguisher_type using table2.
The problem is that lifespan is an int and can change. The usual way to add year to a date is using date + interval '10 year'
The problem is that I want a variable instead of the 10.
At this point i'v this:
CREATE OR REPLACE FUNCTION ext_life_span()
RETURNS trigger AS
$BODY$
DECLARE
new_date timestamptz;
manu_date char(10);
intervalo char(10);
BEGIN
IF (TG_OP = 'INSERT') THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
IF new.manufacturing_date IS DISTINCT FROM old.manufacturing_date THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
END IF;
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ext_life_span() OWNER TO postgres;
CREATE TRIGGER insert_lifespan
AFTER INSERT OR UPDATE OF manufacturing_date
ON "extinguisher"
FOR EACH ROW
EXECUTE PROCEDURE ext_life_span();
I'm getting an error: invalid input syntax for type date: "menu_date"
Am I doing the best approach? Is there an easier way to do it?