3

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?

1 Answer 1

4

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

END IF    /* need semicolon here */

PERFORM pg_notify

Best of luck.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.