CODEWITHSUNDEEP
functionpostgresqldatetriggers
user3194575
user3194575

Reputation: 3

postgresql function/trigger days between two dates

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)"

Upvotes: 0

Views: 1473

Answers (2)

Houari
Houari

Reputation: 5651

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;

Upvotes: 0

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

You didn't post the error, just the statement that's causing the error.

Cutting to the chase: get rid of that insert statement. You're likely cascading the trigger, causing it to call itself over and over again. Setting NEW.dur and then returning NEW is what will set that value in the row you're inserting.

Upvotes: 2

Related Questions