I’ve seen that example, but it can only send a message saying “there is an update”
it cannot send (or I’vnt found how to do it) the table, field, rowid that is updated, to reduce network traffic.
Well, you can transmit any message you want, so you have to come up with your own naming scheme. Something like “event:UPDATE;table:person;Id:6786;new data:(recordDatainJsonFormat)”
Then you need to teach your client how to parse that. And your db how to send those notifications.
I simply send back the name of the event. For example “OrderChanged,ID,ExtraParameter” then I add a comma, and the extra parameter if needed. When I receive the notification I parse it out and take the required action. For example I can check the returned ID and if it matches the order the user is currently working on then I can notify them that another user made changes to the order. I also use it to update shipping info and production status changes. I actually have a field in the order header table called savetype. This field is returned with the notification and tells me specifically what changed in the order ie. production status, order header, order lines, shipping info…
you can’t do that : this is the xojo example program to listen to postgres notification
[code]PgDatabase.Host = HostField.Text
PgDatabase.UserName = UsernameField.Text
PgDatabase.Password = PasswordField.Text
PgDatabase.AppName = App.ExecutableFile.Name
PgDatabase.DatabaseName = DatabaseField.Text
If Not PgDatabase.Connect Then
MsgBox("Couldn’t connect to server: " + PgDatabase.ErrorMessage)
Return
End If
PgDatabase.Listen(“test notification”)
If PgDatabase.Error Then
MsgBox("Couldn’t listen for notification: " + PgDatabase.ErrorMessage)
PgDatabase.Close
Return
End If
MsgBox(“Listening for notifications.”)[/code]
you must provide the string you want to listen to
you can’t use an asterisk to specify begin with to what you want to listen.
so I don’t see how to notify with parameters ?
stopping here - double post here :
https://forum.xojo.com/48519-notify-events-from-postgresql-to-external-listeners
Fwiw, you could probably send a json String. That would let you send several kinds of names propeeties and values.
regarding the payload (parameters) for LISTEN / NOTIFY see <https://xojo.com/issue/13084> incl. a workaround for the missing support in PostgreSQLDatabase.Notify.
For use with triggers the easiest way is to make use of the extension tcn (you have to CREATE EXTENSION tcn; as it is delivered as a built in/official contrib module), see: PostgreSQL: Documentation: 14: F.41. tcn