From: Jonathan Gardner <jgardner@jonathangardner.net>
To: "Anagha Joshi" <ajoshi@nulinkinc.com>, <pgsql-sql@postgresql.org>
Subject: Re: [ADMIN] Notification
Date: Tue, 17 Jun 2003 13:34:23 -0700
User-Agent: KMail/1.5
References: <FF851C7EEB75954F9BCFB5CA117AB1EC61A427@delta.nulinkinc.com>
In-Reply-To: <FF851C7EEB75954F9BCFB5CA117AB1EC61A427@delta.nulinkinc.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200306171334.24931.jgardner@jonathangardner.net>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday 17 June 2003 05:13, Anagha Joshi wrote:
> Hi All,
> I'm new to Postgres. I'm using Postgres-7.2.4
> Can anybody guide me in detail how 'NOTIFY-LISTEN' can be implemented
> progmatically bet'n frontend and backend?
>

Might want to move this to the SQL list.

The idea is that a process will LISTEN for some notification. Another proce=
ss=20
will NOTIFY that notification, and the one listening will get a message.

This is useful for a couple of things.

If you have an app that shows something like customer data, then you might=
=20
want to update the customer info when it changes.

Here's the table:

	CREATE TABLE customer (
		customer_id SERIAL PRIMARY KEY
		, first_name VARCHAR(20) NOT NULL
		, last_name VARCHAR(20) NOT NULL
	);

When displaying the customer info for customer_id 19291, you could do=20
something like:

	LISTEN customer_19291;

Now when someone else goes in and changes the customer info in the database=
,=20
you can have a trigger set up that will NOTIFY.

	CREATE OR REPLACE FUNCTION notify_on_update() RETURNS TRIGGER AS '=09
	BEGIN
		EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.customer_id;
		RETURN NEW;
	END
	' LANGUAGE 'plpgsql';

	CREATE TRIGGER notify_on_update AFTER update ON customer
	FOR EACH ROW EXECUTE PROCEDURE notify_on_update();

Now you set up your application to watch for the notification. When it=20
receives the notification, if can update the displayed information.

Is this a good starting place?

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+73tPWgwF3QvpWNwRAqSQAKC/IsVy45bg4FAy4vwOK0PvBcfbjACg2XWT
D98xYLKLXVuQPkUGTiCVHVE=3D
=3Dkpg2
-----END PGP SIGNATURE-----