CODEWITHSUNDEEP
postgresql
Jithin Kumar S
Jithin Kumar S

Reputation: 873

Automatically set expiry date in Postgres while inserting a record

I have a course table where joining date is now() but need to set an monthly_expiry_day -->which is now + 1 month. How to set this column automatically when we insert a course ?

Upvotes: 1

Views: 4176

Answers (2)

Jithin Kumar S
Jithin Kumar S

Reputation: 873

Also add the trigger to call the function

CREATE TRIGGER set_course_expiry_Trigger 
  AFTER INSERT 
  ON student_course 
  FOR EACH ROW 
  EXECUTE PROCEDURE insert_course_expiry();

Upvotes: 1

user330315
user330315

Reputation:

You need to assign this value in a BEFORE INSERT trigger:

CREATE OR REPLACE FUNCTION insert_course_expiry()
 RETURNS trigger AS
$$ 
BEGIN 
  new.course_expiry = current_date + interval '1 year';
  return new;
END; 
$$ 
LANGUAGE plpgsql;

Then create a trigger:

create trigger set_expiry_trigger 
  before insert on student_course
  for each row
  execute procedure insert_course_expiry();

Upvotes: 1

Related Questions