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.

Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date. Currently I'm attempting this with a rule.

CREATE OR REPLACE RULE "DeadlineInput" AS
    ON INSERT TO "ComLog"
   WHERE new."Deadline" < now() DO INSTEAD  SELECT "ThrowError"('New deadline must be after current time'::bpchar) AS "ThrowError";
COMMENT ON RULE "DeadlineInput" ON "ComLog" IS 'Ensures deadline input is after the current time.';

I'm using a separate function to try to throw an error as when I tried to do it within the Rule it failed as the language was wrong. The following is my function.

-- Function: "ThrowError"(character)

-- DROP FUNCTION "ThrowError"(character);

CREATE OR REPLACE FUNCTION "ThrowError"("ErrorString" text)
  RETURNS void AS
$BODY$

BEGIN

    RAISE EXCEPTION '%', "ErrorString";

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION "ThrowError"(character)
  OWNER TO reuben;

This always produces the error message regardless of the date and i don't know why. As a side question is there a way to make the string passed to my function contain the current time. Something like "ThrowError"('New deadline must be after current time %' now())

Thanks

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

Here's what the current manual says in Rules Versus Triggers:

If checks for valid values are required, and in the case of an invalid value an error message should be generated, it must be done by a trigger.

Consider implementing the check in a trigger, not a rule. The trigger is the perfect tool for the job in your case. On the other hand, it is a known fact that rules are touchy and much harder to master.

share|improve this answer
add comment

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.