const pg = require('pg');
let client = new pg.Client({user: '', database: '', host: '', password: '', port: 5432});
client.connect();
client.query('LISTEN progress_updates');
client.on('notification', function(data) {
   console.log('notification: ', data);
});

-- table
drop table if exists operations;
create table progress ( id serial primary key, percent int default 0 );

-- stored procedure
CREATE OR REPLACE PROCEDURE my_stored_proc() language plpgsql
AS $$
DECLARE
progress_id int;
BEGIN
insert into progress (percent) values (0) returning id into progress_id;
commit;

update progress set percent= 10 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 10}');
-- notify progress_updates, '{"percent": 10}'
perform pg_sleep(5);

update progress set percent= 30 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 30}');
perform pg_sleep(5);

update progress set percent= 70 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 70}');
perform pg_sleep(5);

update progress set percent= 100 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 100}');
END;
$$;

-- trigger
create or replace function notify_progress_trigger() returns trigger
  language plpgsql
as
$$
DECLARE
  payload TEXT;
BEGIN
  raise notice 'TRIGGERED ON %', NOW();
  payload := '{"percent": ' || NEW.percent || '}';
  perform pg_notify('progress_updates', payload);
  -- execute format('notify progress_updates, ''%s''', payload);
  return NEW;
END;
$$;
alter function notify_progress_trigger() owner to postgres;

create trigger watched_progress_trigger
  after insert or update
  on operations
execute procedure notify_progress_trigger();

-- Call stored proc manually
call my_stored_proc();