On Wed, Mar 19, 2008 at 11:37:16AM +1030, Tyler, Mark wrote: > > Oh, you can, you can calculate the name of the NOTIFY dynamically > > in the trigger sending the notify, for example embedding a primary key > value. > > I don't understand how this can work. Surely my subscriber applications > have to listen to the same notification name that the publisher is going > to notify on (unless I am missing something about how NOTIFY/LISTEN > works). Yes I know you can construct that name dynamically prior to > issuing the NOTIFY but if you are changing the notification name then > how do the subscribers know what names to listen on? Do you have a quick > example of your suggestion? Sure. GNUmed is a medical record application. There is always one particular patient "active" in any given frontend. The frontend knows the primary key of this patient. During database setup we use a script to create notification triggers for a bunch of tables which directly or indirectly (via multi-step foreign key paths) link to a patient primary key. The NOTIFY names generated dynamically are of the format like, say, allergy_mod::<pk of patient> where <pk of patient> is replaced by the respective primary key (allergy_mod stands for "modification to allergy table"). Now assume the frontend activates patient 12. It will then issue LISTEN commands for, among others, "allergy_mod::12". Assume a process INSERTing, UPDATEing, or DELETEing data in the allergy table. The notification triggers calculate which patient a modified row belongs to. Let's say it was about patient 12. So the triggers sends a NOTIFY "allergy_mod::12" which the frontend receives. It's great fun to watch people from all across the globe change data on test patients in our public test database. The frontend is written to display such updates in realtime (well, whatever it takes to get the notification delivered). :-) We do, of course, know which PKs we are interested in at any given time. Without that knowledge this scheme wouldn't work. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
