Postgresql, syntax error in IF clause

Question

I'm trying to create a trigger that sets off a function that checks whether there is enough stock for a book when a shipment of the book is inserted into the table shipments. If there is not enough stock, an exception is raised, otherwise the stock is reduced by 1.

This is my code:

CREATE FUNCTION decstock() RETURNS trigger AS $pname$
    BEGIN

        IF (SELECT stock FROM stock WHERE stock.isbn = NEW.ISBN) = 0 THEN
            RAISE EXCEPTION 'There is no stock to ship'
        ELSE
            UPDATE stock
            SET stock = stock-1
            WHERE stock.isbn = NEW.isbn;
        END IF;
    END;
$pname$ LANGUAGE plpgsql;

CREATE TRIGGER hej
BEFORE UPDATE ON shipments
FOR EACH ROW /*Why on each row? Aren't we supposed to only look at the stock for the specified isbn? Also, how do I reference the stock table here?*/
EXECUTE PROCEDURE decstock();

I get an error when running this, it says:

ERROR:  syntax error at or near "ELSE"
LINE 7:   ELSE
          ^

********** Error **********

ERROR: syntax error at or near "ELSE"
SQL state: 42601
Character: 181

I can't find the syntax error I have made here. Any tips?

RAISE EXCEPTION 'There is no stock to ship'; missing a semi-colon — jpw
– jpw, Commented Sep 19, 2015 at 12:52

Craig Ringer · Accepted Answer · 2015-09-19 12:51:08Z

2

RAISE EXCEPTION '....' must be terminated with a semicolon, like any other pl/pgsql statement.

answered Sep 19, 2015 at 12:51

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql, syntax error in IF clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related