#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    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.
    Code:
    ERROR: syntax error at or near "RAISE"
    SQL state: 42601
    Character: 190
    Code:
    -- 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.';
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    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
    Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo