Notify Events From PostgreSQL to External Listeners

Databases
1

I am looking for a example handling asynchronous database events by connected clients, using the Xojo PostgreSQL plugin.

2

Check out Xojo/Example projects/Database/PostgreSQL/Listen and Notify

3

Oh yes of course, dumbo I am. Thanks @Maximilian Tyrtania
I was not aware of the ReceivedNotifications event of PostgreSQLDatabase.

But: @Paul Lefebvre - both examples will never work as is since the entry for the port-number has been forgotten in both.
(Feedback 52600)

4

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 ?

5

Drag a PostgreSQL class into your project. Add the ReceivedNotification event to the class. There is a parameter called aExtra that contains the parameter info from the notification. aName is the name of the notification. aPid is the PID of the process that initiated the notification.

Here is the code from my app.

[code]Sub ReceivedNotification(aName as String, aPid as Integer, aExtra as String)
#if DebugBuild then
Status.Text = “Received notification: '” + aName + "’ from " + Str(aPid) + “;” + aExtra
#endif

Select Case aName

case “server”
dim temp() as String = aExtra.Split(",")
dim Action, Msg as String
if temp.Ubound > -1 Then Action = temp(0)
msg = aExtra.Right(aExtra.Len - (Action.Len + 1))

Select case Action
  
case "OrderChanges"
  if msg <> CurrentSave Then OrderChanges.Append msg
case "Update"
  
case else
  
End Select

End Select
End Sub
[/code]

6

nice, and how do you send the aExtra from the notifier App ?

7

https://www.postgresql.org/docs/9.0/static/sql-notify.html

sql = "NOTIFY aName, 'payload text'"
8

Here is the SQL that creates my function the send a notification, along with the trigger to call the notification function.
As you can see I can access both the new and old recorded returned by the trigger, by specifying NEW.field or OLD.field. Then I can decide whether or not to fire the trigger based on the value of the fields.

[code]
CREATE OR REPLACE FUNCTION notify_orderchanged()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
PERFORM pg_notify(CAST(‘server’ as text), ‘OrderChanges,’ || NEW.id::text || ‘,’ || NEW.savestatus::text )
WHERE NEW.savestatus = ‘1’ or NEW.savestatus = ‘2’ or NEW.savestatus = ‘3’ or NEW.savestatus = ‘9’;
RETURN NEW;
END;
$function$;

DROP TRIGGER IF EXISTS notify_orderchanged on orders;
CREATE TRIGGER notify_orderchanged AFTER INSERT OR UPDATE ON Orders FOR EACH ROW EXECUTE PROCEDURE notify_orderchanged();[/code]