postgres: function with select and notify

Question

So, I have a table which describes positions of some entities in space(for simplicity in one dimension, original problem is related )

    CREATE TABLE positions(
       id VARCHAR (32) UNIQUE NOT NULL,
       position VARCHAR (50) NOT NULL
    );

And I create a trigger which notifies on every update

  CREATE TRIGGER position_trigger AFTER UPDATE ON positions
  FOR EACH ROW
  EXECUTE PROCEDURE notify_position();

and a function:

CREATE FUNCTION notify_position() RETURNS trigger AS $$
DECLARE
  BEGIN
    PERFORM pg_notify(
      'positions',
      TG_TABLE_NAME || ',id,' || NEW.id || ',position,' || NEW.position
    );
    RETURN new;
END;
$$ LANGUAGE plpgsql;

how can I change the function to notify only when there are few entities in the same position.

e.g. consider a table after update

id  |positions
-------------
id1 |10
id2 |10
id3 |11

I need to call notify with a string 'id1,id2'

Probably, I need to select somehow all the entities which have the same position as updated one and create a list of them(a string which contains comma-separated ids). How can I do that?

peterdn · Accepted Answer · 2018-02-15 20:07:20Z

2

From what I understand of your question, you want the following to happen when an entity's position is updated:

If no entities are currently in the new position, do nothing.
Otherwise call notify with a comma-separated list of entity IDs that are currently in the new position.

You can achieve this in your trigger function by using the string_agg aggregation function to construct a list of IDs of matching entities, then checking its length before calling pg_notify:

CREATE FUNCTION notify_position() RETURNS trigger AS $$
DECLARE
  colocated_ids TEXT;
BEGIN
  -- Construct list of IDs of co-located entities.
  colocated_ids := (
    SELECT string_agg(id, ',')
    FROM positions
    WHERE position = NEW.position AND id != NEW.id
  );

  -- Notify listeners if some co-located entities were found.
  IF length(colocated_ids) > 0 THEN
    PERFORM pg_notify('positions', colocated_ids);
  END IF;

  RETURN new;
END;
$$ LANGUAGE plpgsql;

Note that without the AND id != NEW.id check, the ID of the updated entity will also appear in the list. You could avoid this by making this trigger fire BEFORE UPDATE instead of AFTER UPDATE.

answered Feb 15, 2018 at 20:07

peterdn

2,4961 gold badge24 silver badges24 bronze badges

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

1 Comment

kharandziuk Over a year ago

I also found a solution with for-loop. But your answer looks better. Thx!

Collectives™ on Stack Overflow

postgres: function with select and notify

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related