Syntax error at end of input with IF ... ELSE statement

Question

I'm running this code:

CREATE OR REPLACE FUNCTION trg_orders_check()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
  IF NEW.type = 'prescription' AND NEW.prescription_id IS NULL THEN
    RAISE EXCEPTION 'Must include prescription_id';
  ELSE IF NEW.TYPE = 'item' AND NEW.item_id IS NULL THEN
    RAISE EXCEPTION 'Must include item_id';
  ELSE
    RAISE EXCEPTION 'Type must be either prescription or item';
  END IF;
END
$func$;

I get the error:

syntax error at end of input

What's wrong with it?

[As documented in the manual](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS) it's `ELSIF` — , May 09 '22 at 20:13
Man I've been reading through those trying to figure out what I did wrong and completely skipped over that part. Thank you! — Mason Clark, May 09 '22 at 20:17

score 1 · Answer 1 · answered May 14 '22 at 20:32

The syntax error has been pointed out in the comments. It's ELSIF or ELSEIF.

More importantly, your logic is broken. You would reach the exception 'Type must be either prescription or item' when everything is ok.

You most probably want something like this:

CREATE OR REPLACE FUNCTION trg_orders_check()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE NEW.type
   WHEN 'prescription' THEN
      IF NEW.prescription_id IS NULL THEN
         RAISE EXCEPTION 'Must include prescription_id';
      END IF;
   WHEN 'item' THEN
      IF NEW.item_id IS NULL THEN
         RAISE EXCEPTION 'Must include item_id';
      END IF;
   ELSE
      RAISE EXCEPTION 'Type must be either prescription or item';
   END CASE;
END
$func$;

Using a "switched" CASE. See:

Display column name with max value between several columns

You could also use separate IF statements, but this is probably the best way.

Syntax error at end of input with IF ... ELSE statement

1 Answers1