Trigger - Add to a date using a subquery

Question

(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?

Answer 1

You can easily use a variable when doing something like this:

menu_date := menu_date + interval '1' year * num_years

Where num_years holds the number of years that should be added.

You get the error because date 'manu_date' is an incorrect date literal because the string (!) 'manu_dat' is not a valid date. The expression date 'xxxx' requires a date definition in ISO format e.g. date '2013-09-27'

Regarding your trigger:

The statement update extinguisher set life_date = new_date; will update all rows in the table extinguisher. I'm pretty sure that is not what you want. You are missing a where condition for the update.

You also cannot run an update on the table on which the trigger is fired. You simply assign the value to the new record.

I although I absolutely don't understand what you intend with your trigger, I think what you want is much simpler:

CREATE OR REPLACE FUNCTION ext_life_span()
  RETURNS trigger AS
$BODY$
DECLARE
    l_lifespan integer; 
BEGIN

  -- note that this can fail, if type_designation is not unique!!!
  SELECT lifespan 
    into l_lifespan
  FROM extinguisher_type
  WHERE type_designation = new.type_designation_extinguisher_type;

  new.life_date := new.manufacturing_date + interval '1' year * l_lifespan;

  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

---

Edit: depending on your data volumn, you could also get rid of the trigger and the life_date column alltogether and simply calculate that value "on-the-fly" using a view:

select ex.extinguisher_id, 
       ex.manufacturing_date,
       ex.manufacturing_date + interval '1' year * ed.life_span as life_date
from extinguisher ex
  join extinguisher_designation ed 
       -- I'm not sure I understand your data model correctly.
       -- you need to setup a proper FK here e.g. by using the generated ID
    on ed.extinguihser_type = ex.extinguihser_type 

That way the value will always be up-to-date and you never need to worry about triggers.

In a relational database, values that can be derived from existing values should not be stored.