3

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?

klin
  • 112,967
  • 15
  • 204
  • 232
Dominic Jonas
  • 4,717
  • 1
  • 34
  • 77

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
  • 112,967
  • 15
  • 204
  • 232
  • 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