September 2nd, 2012, 09:01 PM
Raise exception from rule
HI I'm trying to find a way to raise an exception if the user attempts to enter a deadline before the current date but all I'm getting is a syntax error.
ERROR: syntax error at or near "RAISE"
SQL state: 42601
-- Rule: "DeadlineInput" ON "ComLog"
DROP RULE "DeadlineInput" ON "ComLog";
CREATE OR REPLACE RULE "DeadlineInput" AS
ON INSERT TO "ComLog" WHERE new."Deadline" < now() DO INSTEAD RAISE EXCEPTION 'New deadlinles must be after the current date %', now();
COMMENT ON RULE "DeadlineInput" ON "ComLog" IS 'Ensures deadline input is after the current time.';
September 3rd, 2012, 02:54 AM
RAISE is a PL/pgSQL command. Rules are written in SQL, so you cannot use an PL/pgSQL in there.
From The Manual:
Originally Posted by The fine manual
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
September 3rd, 2012, 11:21 AM
Thanks, is there anyway of achieving this by writing a pgsql function and calling it in the rule. I tried this but it didn't appear to work, it always failed on input into the table.