Postgresql notify on query result changes

Question

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 · Accepted Answer · 2017-07-13 11:48:02Z

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.

answered Jul 13, 2017 at 11:48

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Dominic Jonas Over a year ago

Very nice solution! I would not have come to that solution and would have kept me at stackoverflow.com/q/25435669/6229375 .

Collectives™ on Stack Overflow

Postgresql notify on query result changes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related