Raise exception from rule

Dev Shed Forums Sponsor:

September 2nd, 2012, 08:01 PM

wookie1

Registered User

Join Date: Sep 2012

Posts: 3

Time spent in forums: 42 m 31 sec
Reputation 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.';

September 3rd, 2012, 01:54 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,683

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 18 m 35 sec
Reputation Power: 284

RAISE is a PL/pgSQL command. Rules are written in SQL, so you cannot use an PL/pgSQL in there.

From The Manual:

Quote:

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

September 3rd, 2012, 10:21 AM

wookie1

Registered User

Join Date: Sep 2012

Posts: 3

Time spent in forums: 42 m 31 sec
Reputation 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.

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Raise exception from rule

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump