postgresql function/trigger days between two dates

Question

I want to create function which calculate days between two dates, and result store in column in table

Function and trigger:

CREATE FUNCTION dur()

    RETURNS TRIGGER
    AS $$
       BEGIN 
          NEW.duration = NEW.dateTO - NEW.dateFROM;
          INSERT INTO calc(duration) VALUES (NEW.duration);
          RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;

 CREATE TRIGGER dur
  BEFORE INSERT ON calc
  FOR EACH ROW EXECUTE PROCEDURE dur();

Table:

CREATE TABLE calc(

   idU SERIAL PRIMARY KEY,
   dateFROM DATE NOT NULL,
   dateTO DATE NOT NULL,
   duration INTEGER );

When I make a new INSERT INTO error appears:

PL/pgSQL function "dur" line 4 at SQL statement SQL statement "INSERT INTO calc(duration) VALUES (NEW.duration)"

Houari Houari · Accepted Answer · 2014-01-14T16:08:22

If i understood, you have to calculate the duration field automatically, if so, you've juste to delete the:

 INSERT INTO calc(duration) VALUES (NEW.duration);

because, you already calculated that field using:

  NEW.duration = NEW.dateTO - NEW.dateFROM;

postgresql function/trigger days between two dates

2 Answers