Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 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.

share|improve this answer
    
That works! Thank you! Now the problem is that the select statement is returning multiple rows :/ –  Olhinho Sep 27 '13 at 11:56
    
@Olhinho: I don't understand your table structure anyway. Why is type_designation_extinguisher_type a text column? Shouldn't that be an integer referencing the "serial" column of the extinguisher_designation table? (assuming that is the primary key of that table) –  a_horse_with_no_name Sep 27 '13 at 12:25
    
That's the name pgmodeler gave to the FK i cant change that. Well i can, once it is on postgres but i didnt yet... –  Olhinho Sep 27 '13 at 14:31
    
But those are column names, not foreign key names. –  a_horse_with_no_name Sep 27 '13 at 14:36
    
It is supposed to be a integer as FK from the serial in table2, but for now i'm using type_designation as FK bc it's easier for me to build examples, type_designation is unique anyway. Later on it is easy to change that back. Or, since type_designation is unique i should eliminate type_id? I will post the solution i used in some hours... can't post it yet, new user :D. Thank you very much for answering it so fast. –  Olhinho Sep 27 '13 at 14:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.