1

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?

Dmitry Bychenko
  • 165,109
  • 17
  • 150
  • 199
Dominic Jonas
  • 4,329
  • 1
  • 29
  • 64

1 Answers1

2

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.

klin
  • 99,138
  • 12
  • 177
  • 203
  • Very nice solution! I would not have come to that solution and would have kept me at https://stackoverflow.com/q/25435669/6229375 . – Dominic Jonas Jul 13 '17 at 12:03