Notification

From ajoshi@nulinkinc.com Tue Jun 17 12:13:31 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 2E91F30EFA9 for ; Tue, 17 Jun 2003 12:13:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 15112-02 for ; Tue, 17 Jun 2003 09:13:20 -0300 (ADT) Received: from TactiCom-MA1.TactiCom.com (unknown [216.235.244.20]) by svr1.postgresql.org (Postfix) with SMTP id AAC2E30F009 for ; Tue, 17 Jun 2003 09:13:19 -0300 (ADT) Received: from [203.124.133.194] by TactiCom-MA1.Nulinkinc.com (NTMail 4.30.0013/NU4135.00.d4f82e0c) with ESMTP id pflfiaaa for ; Tue, 17 Jun 2003 08:13:09 -0400 content-class: urn:content-classes:message Subject: Notification MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C334C9.D1447864" Date: Tue, 17 Jun 2003 17:43:10 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Notification Thread-Index: AcM0yX1PNJNIlqB8EdedngBABabtaA== From: "Anagha Joshi" To: X-Spam-Status: No, hits=4.6 tagged_above=0.0 required=5.0 tests=HTML_70_80, RCVD_IN_DSBL X-Spam-Level: ***** X-Archive-Number: 200306/191 X-Sequence-Number: 9182 This is a multi-part message in MIME format. ------_=_NextPart_001_01C334C9.D1447864 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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? Help appreciated. Thx, Anagha ------_=_NextPart_001_01C334C9.D1447864 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Notification

Hi All,<= /FONT>

I= ’m new to Postgres. I<= /FONT>’m using Postgres-7.2.4

Can anyb= ody guide me in detail how ‘NOTIF= Y-LISTEN’= can be implemented prog= matically bet’n frontend and back= end?

Help app= reciated.

Thx,

Anagha

------_=_NextPart_001_01C334C9.D1447864-- From jgardner@jonathangardner.net Tue Jun 17 20:34:37 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id C0F8830F134 for ; Tue, 17 Jun 2003 20:34:37 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 77876-08 for ; Tue, 17 Jun 2003 17:34:27 -0300 (ADT) Received: from irko.smoothcorp.com (unknown [208.49.241.41]) by svr1.postgresql.org (Postfix) with ESMTP id BA47230F125 for ; Tue, 17 Jun 2003 17:34:26 -0300 (ADT) Received: from jgardner.ifloor.com ([10.0.1.63]) by irko.smoothcorp.com with esmtp (Exim 4.12) id 19SN9t-0000xL-00; Tue, 17 Jun 2003 13:34:25 -0700 Received: from jgardner.ifloor.com (localhost.localdomain [127.0.0.1]) by jgardner.ifloor.com (8.12.8/8.12.8) with ESMTP id h5HKYQwr008539; Tue, 17 Jun 2003 13:34:26 -0700 Received: from localhost (localhost [[UNIX: localhost]]) by jgardner.ifloor.com (8.12.8/8.12.8/Submit) id h5HKYPuJ008537; Tue, 17 Jun 2003 13:34:25 -0700 From: Jonathan Gardner To: "Anagha Joshi" , Subject: Re: [ADMIN] Notification Date: Tue, 17 Jun 2003 13:34:23 -0700 User-Agent: KMail/1.5 References: In-Reply-To: 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> X-SA-Exim-Rcpt-To: ajoshi@nulinkinc.com, pgsql-sql@postgresql.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Archive-Number: 200306/206 X-Sequence-Number: 13964 -----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 (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-----