Skip to content

Instantly share code, notes, and snippets.

@dirkjanfaber
Last active September 11, 2017 05:14
Show Gist options
  • Save dirkjanfaber/f89f77f77c81a1db52af to your computer and use it in GitHub Desktop.
Save dirkjanfaber/f89f77f77c81a1db52af to your computer and use it in GitHub Desktop.
JSON notify message for PostgreSQL
select pg_notify('json', row_to_json(x)::text) as notify
from (select 'hello world' as message, 42 as answer ) x;
@dirkjanfaber
Copy link
Author

In this case 'json' is the channel to listen to, the message to send over the channel is:

{"message":"hello world","answer":42}

Asynchronous notification "json" with payload "{"message":"hello world","answer":42}" received from server process with PID 17306.

Use this code in combination with a 'LISTEN json;'

@dustinsmith1024
Copy link

randomly found this, thanks. Do you have issues with message size ever? I noticed the pg doc recommended never sending messages over 8000 bytes.

@dirkjanfaber
Copy link
Author

I never send too large messages with the notifications. If a message would become too long, I'd probably add a link to the complete message in one of the json fields. Or an id + view on how to fetch the needed extra info.

@9659593293
Copy link

Thanks for your post.It is very use full for me.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment