Search code examples
postgresqltriggersnotify

Postgresql notify on query result changes

I have a sql query

SELECT COUNT(*) 
  FROM (SELECT * 
          FROM recipes 
         WHERE lock != '') AS count

and I want a notification whenever the result changes. It would be ideal when I only get a notification when the value is 0 or >0. Does anyone has a solution approach?

Solution

  • Create a trigger on recipes:

    create or replace function recipes_trigger()
returns trigger language plpgsql as $$
declare
    payload text;
begin
    payload:= exists(select 1 from recipes where lock <> '')::int;
    perform pg_notify('recipes', payload);
    return null;
end $$;

create trigger recipes_trigger
after insert or update or delete on recipes
for each statement execute procedure recipes_trigger();

    A client listening on the channel recipes will get a notification with the payload 0 or 1 after each insert/update/delete on the table.