Postgresql ERROR: syntax error at or near "PERFORM" when inserting a nested IF statement in a procedure

Question

I have the following procedure query that works fine:

CREATE OR REPLACE FUNCTION table_update_notify() RETURNS trigger AS $$
DECLARE
notification_channel text := TG_ARGV[0];
owner_id numeric := TG_ARGV[1];
owner_lat numeric := TG_ARGV[2];
owner_lng numeric := TG_ARGV[3];
trigger_radius numeric := TG_ARGV[4];
nearby_radius numeric := TG_ARGV[5];
changed_lat numeric;
changed_lng numeric;
user_id numeric;
is_close boolean;
name text;
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    changed_lat = NEW.lat;
    changed_lng = NEW.lng;
    user_id = NEW.user_id;
    name = NEW.name;
  ELSE
    changed_lat = OLD.lat;
    changed_lng = OLD.lng;
    user_id = OLD.user_id;
    name = OLD.name;
  END IF;
  -- If updated user's location is within the trigger radius of the trigger owner's location
  IF earth_box(ll_to_earth(owner_lat, owner_lng), trigger_radius) @> ll_to_earth(changed_lat, changed_lng)
  -- Don't notify owner if the owner's location changes
  AND user_id != owner_id
  THEN

  PERFORM pg_notify(notification_channel, json_build_object('user_id', user_id, 'name', name, 'is_close', is_close)::text);

  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

But if I insert another "IF" system after the "THEN", like so, I get an error:

CREATE OR REPLACE FUNCTION table_update_notify() RETURNS trigger AS $$
DECLARE
notification_channel text := TG_ARGV[0];
owner_id numeric := TG_ARGV[1];
owner_lat numeric := TG_ARGV[2];
owner_lng numeric := TG_ARGV[3];
trigger_radius numeric := TG_ARGV[4];
nearby_radius numeric := TG_ARGV[5];
changed_lat numeric;
changed_lng numeric;
user_id numeric;
is_close boolean;
name text;
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    changed_lat = NEW.lat;
    changed_lng = NEW.lng;
    user_id = NEW.user_id;
    name = NEW.name;
  ELSE
    changed_lat = OLD.lat;
    changed_lng = OLD.lng;
    user_id = OLD.user_id;
    name = OLD.name;
  END IF;
  -- If updated user's location is within the trigger radius of the trigger owner's location
  IF earth_box(ll_to_earth(owner_lat, owner_lng), trigger_radius) @> ll_to_earth(changed_lat, changed_lng)
  -- Don't notify owner if the owner's location changes
  AND user_id != owner_id
  THEN

   -- If the user is close enough to the user to be considered nearby
  IF earth_box(ll_to_earth(owner_lat, owner_lng), trigger_radius) @> ll_to_earth(changed_lat, changed_lng) THEN
  is_close = true;
  ELSE
  is_close = false;
  END IF

  PERFORM pg_notify(notification_channel, json_build_object('user_id', user_id, 'name', name, 'is_close', is_close)::text);

  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And the error is:

ERROR:  syntax error at or near "PERFORM"
LINE 39:   PERFORM pg_notify(notification_channel, json_build_object(...

According to my research, this happens when the language is not set to plpgsql, but I clearly am doing that. How can I execute this nested IF statement?

Answer 1

You're missing a semi-colon after an END IF:

END IF    /* need semicolon here */

PERFORM pg_notify

Best of luck.