From jan.verheyden@uz.kuleuven.ac.be Tue Aug  11 13:01:39 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0B59763380B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 10:01:39 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 43240-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 13:01:35 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx2.uzleuven.be (mx2-int.uzleuven.be [134.58.179.12])
	by mail.postgresql.org (Postfix) with ESMTP id A0DDD6336DB
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 10:01:35 -0300 (ADT)
X-AuditID: 863ab30b-b7b68ae00000636c-47-4a816bac38cc
Received: from leo.uz.kuleuven.ac.be (Unknown_Domain [193.190.32.47])
	by mx2.uzleuven.be (UZLeuven Mail Server) with SMTP id
	48.3F.25452.CAB618A4; Tue, 11 Aug 2009 15:01:32 +0200 (CEST)
Received: from CONDOR.uz.kuleuven.ac.be (excclu21.uz.kuleuven.ac.be
	[172.22.3.45])
	by leo.uz.kuleuven.ac.be (8.12.8/8.12.8) with ESMTP id n7BD1W54013939
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 15:01:32 +0200
Received: from ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) by
	CONDOR.uz.kuleuven.ac.be with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 11 Aug 2009 15:01:32 +0200
Received: from EX2007-MBX-2.uz.kuleuven.ac.be ([172.22.12.40]) by
	ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) with mapi;
	Tue, 11 Aug 2009 15:01:32 +0200
From: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Date: Tue, 11 Aug 2009 15:01:31 +0200
Subject: mail alert
Thread-Topic: mail alert
Thread-Index: AcoaVBaDp/oR4ASQR9Cut7qDJUv4NQAL6c5Q
Message-ID: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
Accept-Language: nl-NL, en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: nl-NL, en-US
Content-Type: multipart/alternative;
	boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB49CEX2007MBX2uzk_"
MIME-Version: 1.0
X-OriginalArrivalTime: 11 Aug 2009 13:01:32.0345 (UTC)
	FILETIME=[D95FC290:01CA1A83]
X-Scanned-By: MIMEDefang 2.48 on 193.190.32.47
X-UZLTracking: ksdfkKHKYKPjnqsdKhlfd
X-Brightmail-Tracker: AAAAAgAAAZEQPEw0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=3.702 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001, PYZOR_CHECK=3.7
X-Spam-Level: ***
X-Archive-Number: 200908/49
X-Sequence-Number: 32958

--_000_E30C7040DE22624185BAD4093190B54437BE5DB49CEX2007MBX2uzk_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi All,

I was looking in what way it's possible to alert via mail when some conditi=
ons are true in a database.

Thanks in advance!

Jan

--_000_E30C7040DE22624185BAD4093190B54437BE5DB49CEX2007MBX2uzk_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
.org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
 xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
span.EmailStyle18
	{mso-style-type:personal-reply;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi All,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I was looking in what way it&#8217;s possible to alert=
 via mail
when some conditions are true in a database.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks in advance!<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Jan<o:p></o:p></p>

</div>

</body>

</html>

--_000_E30C7040DE22624185BAD4093190B54437BE5DB49CEX2007MBX2uzk_--



From dbucherml@hsolutions.ch Tue Aug  11 22:36:03 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 956A8633D1B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 19:36:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 81304-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 22:36:00 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from cirrus.hsolutions.ch (cirrus.hsolutions.ch [212.203.66.103])
	by mail.postgresql.org (Postfix) with ESMTP id 5511D6339BF
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 19:36:00 -0300 (ADT)
Received: from [127.0.0.1] (unknown [78.234.64.238])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by cirrus.hsolutions.ch (Postfix) with ESMTPSA id 1C115790001;
	Wed, 12 Aug 2009 00:39:01 +0200 (CEST)
Message-ID: <4A81F244.9070402@hsolutions.ch>
Date: Wed, 12 Aug 2009 00:35:48 +0200
From: Denis BUCHER <dbucherml@hsolutions.ch>
Organization: Horus Digital Solutions =?UTF-8?B?U8Ogcmw=?=
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
CC: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: mail alert
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
In-Reply-To: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/52
X-Sequence-Number: 32961

Hello,

Jan Verheyden a écrit :
> I was looking in what way it’s possible to alert via mail when some
> conditions are true in a database.

a) If the alert is not "very urgent" i.e. you can alter some minutes
later I would do it like this :

1. Create a function that returns what you need, most importantly if the
conditions are met

2. Create a script that does something like "SELECT * FROM function()..."

b) If the email should be sent immediately, you could create a perl
function in the database, launched by a TRIGGER and launching an
"external" script...

Therefore, first you have to know the requirements...

Denis



From robjsargent@gmail.com Wed Aug  12 01:36:40 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 62170634212
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 22:36:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 12124-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 01:36:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from out01.mta.xmission.com (out01.mta.xmission.com [166.70.13.231])
	by mail.postgresql.org (Postfix) with ESMTP id 0797C6327E5
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 22:36:36 -0300 (ADT)
Received: from in01.mta.xmission.com ([166.70.13.51])
	by out01.mta.xmission.com with esmtp (Exim 4.62)
	(envelope-from <robjsargent@gmail.com>)
	id 1Mb2ll-0003QL-Jw; Tue, 11 Aug 2009 19:36:49 -0600
Received: from rsargent.dsl.xmission.com ([199.104.125.126]
 helo=[192.168.0.2])
	by in01.mta.xmission.com with esmtp (Exim 4.69)
	(envelope-from <robjsargent@gmail.com>)
	id 1Mb2lU-0006ds-5Z; Tue, 11 Aug 2009 19:36:32 -0600
Message-ID: <4A821D12.3050500@gmail.com>
Date: Tue, 11 Aug 2009 19:38:26 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Denis BUCHER <dbucherml@hsolutions.ch>
CC: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A81F244.9070402@hsolutions.ch>
In-Reply-To: <4A81F244.9070402@hsolutions.ch>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-XM-SPF: eid=; ; ; mid=; ; ; hst=in01.mta.xmission.com; ; ;
 ip=199.104.125.126;
	; ; frm=robjsargent@gmail.com; ; ; spf=neutral
X-XM-DomainKey: sender_domain=gmail.com; ; ; sender=robjsargent@gmail.com; ; ;
	status=no signature
X-SA-Exim-Connect-IP: 199.104.125.126
X-SA-Exim-Rcpt-To: dbucherml@hsolutions.ch, jan.verheyden@uz.kuleuven.ac.be,
	pgsql-sql@postgresql.org
X-SA-Exim-Mail-From: robjsargent@gmail.com
X-Spam-DCC: XMission; sa01 1397; Body=1 Fuz1=1 Fuz2=1 
X-Spam-Combo: ;Denis BUCHER <dbucherml@hsolutions.ch>
X-Spam-Relay-Country: 
Subject: Re: mail alert
X-SA-Exim-Version: 4.2.1 (built Thu, 25 Oct 2007 00:26:12 +0000)
X-SA-Exim-Scanned: Yes (on in01.mta.xmission.com)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.11 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, RCVD_IN_DNSWL_LOW=-1
X-Spam-Level: 
X-Archive-Number: 200908/53
X-Sequence-Number: 32962

Denis BUCHER wrote:
> Hello,
>
> Jan Verheyden a écrit :
>   
>> I was looking in what way it’s possible to alert via mail when some
>> conditions are true in a database.
>>     
>
> a) If the alert is not "very urgent" i.e. you can alter some minutes
> later I would do it like this :
>
> 1. Create a function that returns what you need, most importantly if the
> conditions are met
>
> 2. Create a script that does something like "SELECT * FROM function()..."
>
> b) If the email should be sent immediately, you could create a perl
> function in the database, launched by a TRIGGER and launching an
> "external" script...
>
> Therefore, first you have to know the requirements...
>
> Denis
>
>   
see "check_postgres" for nagios-style monitoring
see \o /tmp/alert_data_file
and \! mailx -s "alert" me@work.com /tmp/alert_data_file




From ramasubramanian.g@renaissance-it.com Wed Aug  12 04:00:59 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5A97A634C2B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 01:00:59 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 42205-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 04:00:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from renaissance-it.com (mail.renaissance-it.com [164.164.87.55])
	by mail.postgresql.org (Postfix) with ESMTP id 5AC386327E5
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 01:00:52 -0300 (ADT)
Received: (qmail 2053 invoked by uid 109); 12 Aug 2009 04:01:21 -0000
Received: from 192.168.1.6 by Mailsrv (envelope-from
	<ramasubramanian.g@renaissance-it.com>,
	uid 112) with qmail-scanner-1.25st
	(spamassassin: 3.1.7-deb. perlscan: 1.25st.  
	Clear:RC:1(192.168.1.6):.
	Processed in 0.064657 secs); 12 Aug 2009 04:01:21 -0000
Received: from unknown (HELO ramasubramanian)
 (ramasubramanian.g@[192.168.1.6])
	(envelope-sender <ramasubramanian.g@renaissance-it.com>)
	by renaissance-it.com (qmail-ldap-1.03) with SMTP
	for <jan.verheyden@uz.kuleuven.ac.be>; 12 Aug 2009 04:01:20 -0000
Message-ID: <EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
From: "ramasubramanian" <ramasubramanian.g@renaissance-it.com>
To: "Jan Verheyden" <jan.verheyden@uz.kuleuven.ac.be>,
	<pgsql-sql@postgresql.org>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
Subject: Re: mail alert
Date: Wed, 12 Aug 2009 09:31:13 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_010A_01CA1B2F.A24B7EF0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/55
X-Sequence-Number: 32964

This is a multi-part message in MIME format.

------=_NextPart_000_010A_01CA1B2F.A24B7EF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,
    Can you just tell me whether your database is place on which =
server(linux/or windows or..)?
  ----- Original Message -----=20
  From: Jan Verheyden=20
  To: 'pgsql-sql@postgresql.org'=20
  Sent: Tuesday, August 11, 2009 6:31 PM
  Subject: [SQL] mail alert


  Hi All,

  =20

  I was looking in what way it's possible to alert via mail when some =
conditions are true in a database.

  =20

  Thanks in advance!

  =20

  Jan

------=_NextPart_000_010A_01CA1B2F.A24B7EF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:v =3D=20
"urn:schemas-microsoft-com:vml" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word" xmlns:x =3D=20
"urn:schemas-microsoft-com:office:excel" xmlns:p =3D=20
"urn:schemas-microsoft-com:office:powerpoint" xmlns:a =3D=20
"urn:schemas-microsoft-com:office:access" xmlns:dt =3D=20
"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s =3D=20
"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs =3D=20
"urn:schemas-microsoft-com:rowset" xmlns:z =3D "#RowsetSchema" xmlns:b =
=3D=20
"urn:schemas-microsoft-com:office:publisher" xmlns:ss =3D=20
"urn:schemas-microsoft-com:office:spreadsheet" xmlns:c =3D=20
"urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc =3D=20
"urn:schemas-microsoft-com:office:odc" xmlns:oa =3D=20
"urn:schemas-microsoft-com:office:activation" xmlns:html =3D=20
"http://www.w3.org/TR/REC-html40" xmlns:q =3D=20
"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc =3D=20
"http://microsoft.com/officenet/conferencing" XMLNS:D =3D "DAV:" =
XMLNS:Repl =3D=20
"http://schemas.microsoft.com/repl/" xmlns:mt =3D=20
"http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2 =3D=20
"http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda =3D=20
"http://www.passport.com/NameSpace.xsd" xmlns:ois =3D=20
"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir =3D=20
"http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds =3D=20
"http://www.w3.org/2000/09/xmldsig#" xmlns:dsp =3D=20
"http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc =3D=20
"http://schemas.microsoft.com/data/udc" xmlns:xsd =3D=20
"http://www.w3.org/2001/XMLSchema" xmlns:sub =3D=20
"http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec =
=3D=20
"http://www.w3.org/2001/04/xmlenc#" xmlns:sp =3D=20
"http://schemas.microsoft.com/sharepoint/" xmlns:sps =3D=20
"http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi =3D=20
"http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs =3D=20
"http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf =3D=20
"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p =3D=20
"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf =3D=20
"http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss =3D=20
"http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi =3D=20
"http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi =3D=20
"http://schemas.openxmlformats.org/package/2006/digital-signature" =
xmlns:mver =3D=20
"http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m =
=3D=20
"http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels =3D=20
"http://schemas.openxmlformats.org/package/2006/relationships" =
xmlns:spwp =3D=20
"http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t =3D=20
"http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m =
=3D=20
"http://schemas.microsoft.com/exchange/services/2006/messages" =
xmlns:pptsl =3D=20
"http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl =
=3D=20
"http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksSe=
rvice"=20
XMLNS:Z =3D "urn:schemas-microsoft-com:" xmlns:st =3D "=01"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.5512" name=3DGENERATOR>
<STYLE>@font-face {
	font-family: Calibri;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
	FONT-SIZE: 11pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
LI.MsoNormal {
	FONT-SIZE: 11pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
DIV.MsoNormal {
	FONT-SIZE: 11pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
A:link {
	COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlink {
	COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
A:visited {
	COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
	COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.EmailStyle17 {
	COLOR: windowtext; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: =
personal
}
SPAN.EmailStyle18 {
	COLOR: #1f497d; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: =
personal-reply
}
.MsoChpDefault {
	FONT-SIZE: 10pt; mso-style-type: export-only
}
DIV.Section1 {
	page: Section1
}
</STYLE>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=3DEN-US vLink=3Dpurple link=3Dblue bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; Can you just tell me =
whether=20
your database is place on which server(linux/or windows =
or..)?</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
  <A title=3Djan.verheyden@uz.kuleuven.ac.be=20
  href=3D"mailto:jan.verheyden@uz.kuleuven.ac.be">Jan Verheyden</A> =
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3Dpgsql-sql@postgresql.org=20
  =
href=3D"mailto:'pgsql-sql@postgresql.org'">'pgsql-sql@postgresql.org'</A>=
 </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, August 11, 2009 =
6:31=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] mail alert</DIV>
  <DIV><BR></DIV>
  <DIV class=3DSection1>
  <P class=3DMsoNormal>Hi All,<o:p></o:p></P>
  <P class=3DMsoNormal><o:p>&nbsp;</o:p></P>
  <P class=3DMsoNormal>I was looking in what way it=92s possible to =
alert via mail=20
  when some conditions are true in a database.<o:p></o:p></P>
  <P class=3DMsoNormal><o:p>&nbsp;</o:p></P>
  <P class=3DMsoNormal>Thanks in advance!<o:p></o:p></P>
  <P class=3DMsoNormal><o:p>&nbsp;</o:p></P>
  <P =
class=3DMsoNormal>Jan<o:p></o:p></P></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_010A_01CA1B2F.A24B7EF0--




From shoaibmir@gmail.com Wed Aug  12 06:15:06 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6EBB26337C2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 03:15:06 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 64769-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 06:15:03 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f183.google.com (mail-yw0-f183.google.com
	[209.85.211.183])
	by mail.postgresql.org (Postfix) with ESMTP id 48436632B7F
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 03:15:03 -0300 (ADT)
Received: by ywh13 with SMTP id 13so5370498ywh.15
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 23:15:02 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=zbPWHq0mClGmfZR6fyXsvcUItoRCsoQWorGjivmd2M4=;
	b=CAorCl2wCqBefZydDvxFGBLo9y3iyCg8zUPPaGXgNywlHt/5SvWKyKJ9wEQgVeWpyP
	tU781UYshx1J2Khl4LB0teXpd2npBtc3/hTeGXQI9oPrnxJiI/hLveSR34MfMzoz8cex
	GOjK6esSCBjQ+btotryan8VDzMEWMypWBmxxI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=s1Hbty7RIEBmhJqQT9CJKQ8YOIjDGNsx6tWssrpASMKjCROotsnwyJpwTi1lcXg80V
	nx9WtZCcvrBjHCwSkjQNXu4PdVGLt7VTo+0KBL5vWSlP534NApeWZryTIw22PPp6KQEX
	/8hD6k74iJonjZ2YVoWbOie8IuaveVGM7YO5Q=
MIME-Version: 1.0
Received: by 10.91.161.39 with SMTP id n39mr388344ago.88.1250057702497; Tue,
	11 Aug 2009 23:15:02 -0700 (PDT)
In-Reply-To: <EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
Date: Wed, 12 Aug 2009 16:15:02 +1000
Message-ID: <bf54be870908112315k3de42370iacc59e48d15c3912@mail.gmail.com>
Subject: Re: mail alert
From: Shoaib Mir <shoaibmir@gmail.com>
To: ramasubramanian <ramasubramanian.g@renaissance-it.com>
Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0016362844c64e5f930470ebbded
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/56
X-Sequence-Number: 32965

--0016362844c64e5f930470ebbded
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

> ----- Original Message -----
> *From:* Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
> *To:* 'pgsql-sql@postgresql.org' <%27pgsql-sql@postgresql.org%27>
> *Sent:* Tuesday, August 11, 2009 6:31 PM
> *Subject:* [SQL] mail alert
>
>  Hi All,
>
>
>
> I was looking in what way it=92s possible to alert via mail when some
> conditions are true in a database.
>
>
>
>
I guess you might be able to do that by using PLPython, PLPerl or PLTcl and
using the standard functions for doing emails in there.

--=20
Shoaib Mir
http://shoaibmir.wordpress.com/

--0016362844c64e5f930470ebbded
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

<br><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"b=
order-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; paddin=
g-left: 1ex;"><div vlink=3D"purple" link=3D"blue" bgcolor=3D"#ffffff" lang=
=3D"EN-US"><blockquote dir=3D"ltr" style=3D"border-left: 2px solid rgb(0, 0=
, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right=
: 0px;">

  <div style=3D"font-family: arial; font-style: normal; font-variant: norma=
l; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adj=
ust: none; font-stretch: normal;">----- Original Message ----- </div>
  <div style=3D"background: rgb(228, 228, 228) none repeat scroll 0% 0%; -m=
oz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -mo=
z-background-inline-policy: -moz-initial; font-family: arial; font-style: n=
ormal; font-variant: normal; font-weight: normal; font-size: 10pt; line-hei=
ght: normal; font-size-adjust: none; font-stretch: normal;">
<b>From:</b>=20
  <a title=3D"jan.verheyden@uz.kuleuven.ac.be" href=3D"mailto:jan.verheyden=
@uz.kuleuven.ac.be" target=3D"_blank">Jan Verheyden</a> </div>
  <div style=3D"font-family: arial; font-style: normal; font-variant: norma=
l; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adj=
ust: none; font-stretch: normal;"><b>To:</b> <a title=3D"pgsql-sql@postgres=
ql.org" href=3D"mailto:%27pgsql-sql@postgresql.org%27" target=3D"_blank">&#=
39;pgsql-sql@postgresql.org&#39;</a> </div>

  <div style=3D"font-family: arial; font-style: normal; font-variant: norma=
l; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adj=
ust: none; font-stretch: normal;"><b>Sent:</b> Tuesday, August 11, 2009 6:3=
1=20
  PM</div>
  <div style=3D"font-family: arial; font-style: normal; font-variant: norma=
l; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adj=
ust: none; font-stretch: normal;"><b>Subject:</b> [SQL] mail alert</div>

  <div><br></div>
  <div>
  <p>Hi All,</p>
  <p>=A0</p>
  <p>I was looking in what way it=92s possible to alert via mail=20
  when some conditions are true in a database.</p>
  <p>=A0</p>
  </div></blockquote></div></blockquote><div><br>I guess you might be able =
to do that by using PLPython, PLPerl or PLTcl and using the standard functi=
ons for doing emails in there.<br></div></div><br>-- <br>Shoaib Mir<br>
<a href=3D"http://shoaibmir.wordpress.com/">http://shoaibmir.wordpress.com/=
</a><br>

--0016362844c64e5f930470ebbded--



From jan.verheyden@uz.kuleuven.ac.be Wed Aug  12 07:29:56 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 2965C6327E5
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 04:29:56 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 25945-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 07:29:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx2.uzleuven.be (mx2-int.uzleuven.be [134.58.179.12])
	by mail.postgresql.org (Postfix) with ESMTP id E2AFF63223F
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 04:29:44 -0300 (ADT)
X-AuditID: 863ab30b-b7b68ae00000636c-02-4a826f67bc16
Received: from leo.uz.kuleuven.ac.be (Unknown_Domain [193.190.32.47])
	by mx2.uzleuven.be (UZLeuven Mail Server) with SMTP id
	21.71.25452.76F628A4; Wed, 12 Aug 2009 09:29:43 +0200 (CEST)
Received: from CONDOR.uz.kuleuven.ac.be (excclu21.uz.kuleuven.ac.be
	[172.22.3.45])
	by leo.uz.kuleuven.ac.be (8.12.8/8.12.8) with ESMTP id n7C7Tg54018771;
	Wed, 12 Aug 2009 09:29:42 +0200
Received: from ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) by
	CONDOR.uz.kuleuven.ac.be with Microsoft SMTPSVC(6.0.3790.1830);
	Wed, 12 Aug 2009 09:29:42 +0200
Received: from EX2007-MBX-2.uz.kuleuven.ac.be ([172.22.12.40]) by
	ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) with mapi;
	Wed, 12 Aug 2009 09:29:42 +0200
From: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
To: "'ramasubramanian'" <ramasubramanian.g@renaissance-it.com>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Date: Wed, 12 Aug 2009 09:29:41 +0200
Subject: Re: mail alert
Thread-Topic: [SQL] mail alert
Thread-Index: AcobAYXLaPzXEvscSQCUnbh3MGurmQAHRPhQ
Message-ID: 
 <E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
In-Reply-To: <EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
Accept-Language: nl-NL, en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: nl-NL, en-US
Content-Type: multipart/alternative;
	boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_"
MIME-Version: 1.0
X-OriginalArrivalTime: 12 Aug 2009 07:29:42.0499 (UTC)
	FILETIME=[A8981B30:01CA1B1E]
X-Scanned-By: MIMEDefang 2.48 on 193.190.32.47
X-UZLTracking: ksdfkKHKYKPjnqsdKhlfd
X-Brightmail-Tracker: AAAAAQAAAZE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.307 tagged_above=-10 required=5 tests=AWL=0.802,
	BAYES_05=-1.11, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/62
X-Sequence-Number: 32971

--_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

It's on Windows

From: ramasubramanian [mailto:ramasubramanian.g@renaissance-it.com]
Sent: Wednesday, August 12, 2009 6:01 AM
To: Jan Verheyden; pgsql-sql@postgresql.org
Subject: Re: [SQL] mail alert

Hi,
    Can you just tell me whether your database is place on which server(lin=
ux/or windows or..)?
----- Original Message -----
From: Jan Verheyden<mailto:jan.verheyden@uz.kuleuven.ac.be>
To: 'pgsql-sql@postgresql.org'<mailto:'pgsql-sql@postgresql.org'>
Sent: Tuesday, August 11, 2009 6:31 PM
Subject: [SQL] mail alert

Hi All,

I was looking in what way it's possible to alert via mail when some conditi=
ons are true in a database.

Thanks in advance!

Jan

--_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
.org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
 xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
span.EmailStyle18
	{mso-style-type:personal;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
span.EmailStyle19
	{mso-style-type:personal-reply;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body bgcolor=3Dwhite lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><span style=3D'color:#1F497D'>It&#8217;s on Windows<o:=
p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'color:#1F497D'><o:p>&nbsp;</o:p></span>=
</p>

<div>

<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in =
0in 0in'>

<p class=3DMsoNormal><b><span style=3D'font-size:10.0pt;font-family:"Tahoma=
","sans-serif"'>From:</span></b><span
style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'> ramasubramani=
an
[mailto:ramasubramanian.g@renaissance-it.com] <br>
<b>Sent:</b> Wednesday, August 12, 2009 6:01 AM<br>
<b>To:</b> Jan Verheyden; pgsql-sql@postgresql.org<br>
<b>Subject:</b> Re: [SQL] mail alert<o:p></o:p></span></p>

</div>

</div>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<div>

<p class=3DMsoNormal><span style=3D'font-size:10.0pt;font-family:"Arial","s=
ans-serif"'>Hi,</span><span
style=3D'font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p=
></span></p>

</div>

<div>

<p class=3DMsoNormal><span style=3D'font-size:10.0pt;font-family:"Arial","s=
ans-serif"'>&nbsp;&nbsp;&nbsp;
Can you just tell me whether your database is place on which server(linux/o=
r
windows or..)?</span><span style=3D'font-size:12.0pt;font-family:"Times New=
 Roman","serif"'><o:p></o:p></span></p>

</div>

<blockquote style=3D'border:none;border-left:solid black 1.5pt;padding:0in =
0in 0in 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>

<div>

<p class=3DMsoNormal><span style=3D'font-size:10.0pt;font-family:"Arial","s=
ans-serif"'>-----
Original Message ----- <o:p></o:p></span></p>

</div>

<div>

<p class=3DMsoNormal style=3D'background:#E4E4E4'><b><span style=3D'font-si=
ze:10.0pt;
font-family:"Arial","sans-serif"'>From:</span></b><span style=3D'font-size:=
10.0pt;
font-family:"Arial","sans-serif"'> <a
href=3D"mailto:jan.verheyden@uz.kuleuven.ac.be"
title=3D"jan.verheyden@uz.kuleuven.ac.be">Jan Verheyden</a> <o:p></o:p></sp=
an></p>

</div>

<div>

<p class=3DMsoNormal><b><span style=3D'font-size:10.0pt;font-family:"Arial"=
,"sans-serif"'>To:</span></b><span
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif"'> <a
href=3D"mailto:'pgsql-sql@postgresql.org'" title=3D"pgsql-sql@postgresql.or=
g">'pgsql-sql@postgresql.org'</a>
<o:p></o:p></span></p>

</div>

<div>

<p class=3DMsoNormal><b><span style=3D'font-size:10.0pt;font-family:"Arial"=
,"sans-serif"'>Sent:</span></b><span
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif"'> Tuesday, Augus=
t 11,
2009 6:31 PM<o:p></o:p></span></p>

</div>

<div>

<p class=3DMsoNormal><b><span style=3D'font-size:10.0pt;font-family:"Arial"=
,"sans-serif"'>Subject:</span></b><span
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif"'> [SQL] mail ale=
rt<o:p></o:p></span></p>

</div>

<div>

<p class=3DMsoNormal><span style=3D'font-size:12.0pt;font-family:"Times New=
 Roman","serif"'><o:p>&nbsp;</o:p></span></p>

</div>

<p class=3DMsoNormal>Hi All,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I was looking in what way it&#8217;s possible to alert=
 via mail when
some conditions are true in a database.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks in advance!<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Jan<o:p></o:p></p>

</blockquote>

</div>

</body>

</html>

--_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_--



From jan.verheyden@uz.kuleuven.ac.be Wed Aug  12 13:27:54 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 202D76337C2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 10:27:54 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 40730-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 13:27:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx2.uzleuven.be (mx2-int.uzleuven.be [134.58.179.12])
	by mail.postgresql.org (Postfix) with ESMTP id 3973D633362
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 10:27:49 -0300 (ADT)
X-AuditID: 863ab30b-b7b68ae00000636c-08-4a82c351a0e4
Received: from leo.uz.kuleuven.ac.be (Unknown_Domain [193.190.32.47])
	by mx2.uzleuven.be (UZLeuven Mail Server) with SMTP id
	65.26.25452.153C28A4; Wed, 12 Aug 2009 15:27:45 +0200 (CEST)
Received: from CONDOR.uz.kuleuven.ac.be (excclu21.uz.kuleuven.ac.be
	[172.22.3.45])
	by leo.uz.kuleuven.ac.be (8.12.8/8.12.8) with ESMTP id n7CDQn5C011981;
	Wed, 12 Aug 2009 15:27:44 +0200
Received: from ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) by
	CONDOR.uz.kuleuven.ac.be with Microsoft SMTPSVC(6.0.3790.1830);
	Wed, 12 Aug 2009 15:27:27 +0200
Received: from EX2007-MBX-2.uz.kuleuven.ac.be ([172.22.12.40]) by
	ex12-ch2-a.uz.kuleuven.ac.be ([172.22.3.114]) with mapi;
	Wed, 12 Aug 2009 15:27:26 +0200
From: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
To: "'Rob Sargent'" <robjsargent@gmail.com>, "'Denis BUCHER'"
	<dbucherml@hsolutions.ch>
CC: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Date: Wed, 12 Aug 2009 15:27:25 +0200
Subject: Re: mail alert
Thread-Topic: [SQL] mail alert
Thread-Index: Acoa7VeAuKMEB5SMS82yShzCVd2p5wAYVSFA
Message-ID: 
 <E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A81F244.9070402@hsolutions.ch> <4A821D12.3050500@gmail.com>
In-Reply-To: <4A821D12.3050500@gmail.com>
Accept-Language: nl-NL, en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: nl-NL, en-US
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
MIME-Version: 1.0
X-OriginalArrivalTime: 12 Aug 2009 13:27:27.0156 (UTC)
	FILETIME=[A286D740:01CA1B50]
X-Scanned-By: MIMEDefang 2.48 on 193.190.32.47
X-UZLTracking: ksdfkKHKYKPjnqsdKhlfd
X-Brightmail-Tracker: AAAAAQAAAZE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/64
X-Sequence-Number: 32973

SGksDQoNCkkgZ290IGEgYml0IHN0dWNrLi4uIA0KSSB3YXMgbG9va2luZyBmb3IgYSBzb2x1dGlv
biBmb3Igb3B0aW9uIGEpDQoNCk1heWJlIEknbGwgZmlyc3QgZXhwbGFpbiB0aGUgc2l0dWF0aW9u
IGEgYml0IG1vcmU6DQoNCkkgaGF2ZSBvbmUgZGF0YWJhc2UgZm9yIHBhdGllbnQgcmVnaXN0cmF0
aW9uDQpBbm90aGVyIG9uZSBmb3IgaW1hZ2Ugc3RvcmFnZQ0KQW5kIGEgdGhpcmQgb25lIGZvciBi
b29rIGtlZXBpbmcNCkEgcGF0aWVudCBzaG91bGQgYmUgcmVnaXN0ZXJlZCBmaXJzdCBiZWZvcmUg
dGhlIGltYWdlcyBhcmUgc3RvcmVkLCBzbyBpZiB0aGVyZSBpcyBhIG5ldyBsaW5lIGluIHRoZSBz
ZWNvbmQgZGF0YWJhc2Ugd2l0aCBhbiBpZCB3aGljaCBkb2VzIG5vdCBleGlzdCB5ZXQsIGl0IGhh
cyB0byBiZSBub3RpZmllZCBpbiB0aGUgYm9vayBrZWVwaW5nIGRhdGFiYXNlLg0KDQpOb3cgdGhl
IHF1ZXN0aW9uczoNCgkxKSBDYW4gSSBkbyB0aGlzIHdpdGggdGhlIGlubmVyIGpvaW4gKHRhYmxl
cyBzdWJqZWN0X2lkIGZyb20gREIxLCBwYXRfaWQgZnJvbSBEQjIpLCB0aGVyZSBpdCBpcyB0d28g
ZGlmZmVyZW50IGRhdGFiYXNlcyANCgkyKSBPbmNlIGl0IGlzIG5vdGlmaWVkIGluIHRoZSBib29r
IGtlZXBpbmcgdGhhdCBpcyBub3QgcmVnaXN0ZXJlZCB5ZXQsIGlzIGl0IGJlc3QgdG8gcG9sbCBv
biB0aGlzIGNvbHVtbiB0byBzZW5kIGEgd2FybmluZywgb3IgdXNlIGEgdHJpZ2dlcj8/DQoNClRo
YW5rcyEhDQoNCg0KSmFuDQotLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0KRnJvbTogUm9iIFNh
cmdlbnQgW21haWx0bzpyb2Jqc2FyZ2VudEBnbWFpbC5jb21dIA0KU2VudDogV2VkbmVzZGF5LCBB
dWd1c3QgMTIsIDIwMDkgMzozOCBBTQ0KVG86IERlbmlzIEJVQ0hFUg0KQ2M6IEphbiBWZXJoZXlk
ZW47ICdwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcnDQpTdWJqZWN0OiBSZTogW1NRTF0gbWFpbCBh
bGVydA0KDQpEZW5pcyBCVUNIRVIgd3JvdGU6DQo+IEhlbGxvLA0KPg0KPiBKYW4gVmVyaGV5ZGVu
IGEgw6ljcml0IDoNCj4gICANCj4+IEkgd2FzIGxvb2tpbmcgaW4gd2hhdCB3YXkgaXTigJlzIHBv
c3NpYmxlIHRvIGFsZXJ0IHZpYSBtYWlsIHdoZW4gc29tZQ0KPj4gY29uZGl0aW9ucyBhcmUgdHJ1
ZSBpbiBhIGRhdGFiYXNlLg0KPj4gICAgIA0KPg0KPiBhKSBJZiB0aGUgYWxlcnQgaXMgbm90ICJ2
ZXJ5IHVyZ2VudCIgaS5lLiB5b3UgY2FuIGFsdGVyIHNvbWUgbWludXRlcw0KPiBsYXRlciBJIHdv
dWxkIGRvIGl0IGxpa2UgdGhpcyA6DQo+DQo+IDEuIENyZWF0ZSBhIGZ1bmN0aW9uIHRoYXQgcmV0
dXJucyB3aGF0IHlvdSBuZWVkLCBtb3N0IGltcG9ydGFudGx5IGlmIHRoZQ0KPiBjb25kaXRpb25z
IGFyZSBtZXQNCj4NCj4gMi4gQ3JlYXRlIGEgc2NyaXB0IHRoYXQgZG9lcyBzb21ldGhpbmcgbGlr
ZSAiU0VMRUNUICogRlJPTSBmdW5jdGlvbigpLi4uIg0KPg0KPiBiKSBJZiB0aGUgZW1haWwgc2hv
dWxkIGJlIHNlbnQgaW1tZWRpYXRlbHksIHlvdSBjb3VsZCBjcmVhdGUgYSBwZXJsDQo+IGZ1bmN0
aW9uIGluIHRoZSBkYXRhYmFzZSwgbGF1bmNoZWQgYnkgYSBUUklHR0VSIGFuZCBsYXVuY2hpbmcg
YW4NCj4gImV4dGVybmFsIiBzY3JpcHQuLi4NCj4NCj4gVGhlcmVmb3JlLCBmaXJzdCB5b3UgaGF2
ZSB0byBrbm93IHRoZSByZXF1aXJlbWVudHMuLi4NCj4NCj4gRGVuaXMNCj4NCj4gICANCnNlZSAi
Y2hlY2tfcG9zdGdyZXMiIGZvciBuYWdpb3Mtc3R5bGUgbW9uaXRvcmluZw0Kc2VlIFxvIC90bXAv
YWxlcnRfZGF0YV9maWxlDQphbmQgXCEgbWFpbHggLXMgImFsZXJ0IiBtZUB3b3JrLmNvbSAvdG1w
L2FsZXJ0X2RhdGFfZmlsZQ0KDQo=



From robjsargent@gmail.com Wed Aug  12 14:02:01 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 13BD463223F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 11:02:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 09619-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 14:01:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from out01.mta.xmission.com (out01.mta.xmission.com [166.70.13.231])
	by mail.postgresql.org (Postfix) with ESMTP id CC97A633362
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 11:01:18 -0300 (ADT)
Received: from in02.mta.xmission.com ([166.70.13.52])
	by out01.mta.xmission.com with esmtp (Exim 4.62)
	(envelope-from <robjsargent@gmail.com>)
	id 1MbEOS-0008EJ-N3; Wed, 12 Aug 2009 08:01:32 -0600
Received: from [208.187.158.94] (helo=[172.16.1.22])
	by in02.mta.xmission.com with esmtpsa (TLSv1:AES256-SHA:256)
	(Exim 4.69) (envelope-from <robjsargent@gmail.com>)
	id 1MbEOB-0000Zu-A7; Wed, 12 Aug 2009 08:01:15 -0600
Message-ID: <4A82CB26.3070208@gmail.com>
Date: Wed, 12 Aug 2009 08:01:10 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
CC: 'Denis BUCHER' <dbucherml@hsolutions.ch>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A81F244.9070402@hsolutions.ch> <4A821D12.3050500@gmail.com>
	<E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
In-Reply-To: 
 <E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
X-XM-SPF: eid=; ; ; mid=; ; ; hst=in02.mta.xmission.com; ; ;
 ip=208.187.158.94;
	; ; frm=robjsargent@gmail.com; ; ; spf=neutral
X-SA-Exim-Connect-IP: 208.187.158.94
X-SA-Exim-Rcpt-To: jan.verheyden@uz.kuleuven.ac.be, dbucherml@hsolutions.ch,
	pgsql-sql@postgresql.org, robjsargent@gmail.com
X-SA-Exim-Mail-From: robjsargent@gmail.com
X-Spam-DCC: XMission; sa01 1397; Body=1 Fuz1=1 Fuz2=1 
X-Spam-Combo: ;Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
X-Spam-Relay-Country: 
Subject: Re: mail alert
X-SA-Exim-Version: 4.2.1 (built Thu, 25 Oct 2007 00:26:12 +0000)
X-SA-Exim-Scanned: Yes (on in02.mta.xmission.com)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.327 tagged_above=-10 required=5 tests=AWL=1.272,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/57
X-Sequence-Number: 32966

It looks to me like you want Dennis's #2.  Lauch a mailer script from a 
trigger function.  (Function can be any language you're familiar with 
including pgsql if you wish to take advantage of
    "\! your-mail-script"

Jan Verheyden wrote:
> Hi,
>
> I got a bit stuck... 
> I was looking for a solution for option a)
>
> Maybe I'll first explain the situation a bit more:
>
> I have one database for patient registration
> Another one for image storage
> And a third one for book keeping
> A patient should be registered first before the images are stored, so if there is a new line in the second database with an id which does not exist yet, it has to be notified in the book keeping database.
>
> Now the questions:
> 	1) Can I do this with the inner join (tables subject_id from DB1, pat_id from DB2), there it is two different databases 
> 	2) Once it is notified in the book keeping that is not registered yet, is it best to poll on this column to send a warning, or use a trigger??
>
> Thanks!!
>
>
> Jan
> -----Original Message-----
> From: Rob Sargent [mailto:robjsargent@gmail.com] 
> Sent: Wednesday, August 12, 2009 3:38 AM
> To: Denis BUCHER
> Cc: Jan Verheyden; 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] mail alert
>
> Denis BUCHER wrote:
>   
>> Hello,
>>
>> Jan Verheyden a écrit :
>>   
>>     
>>> I was looking in what way it’s possible to alert via mail when some
>>> conditions are true in a database.
>>>     
>>>       
>> a) If the alert is not "very urgent" i.e. you can alter some minutes
>> later I would do it like this :
>>
>> 1. Create a function that returns what you need, most importantly if the
>> conditions are met
>>
>> 2. Create a script that does something like "SELECT * FROM function()..."
>>
>> b) If the email should be sent immediately, you could create a perl
>> function in the database, launched by a TRIGGER and launching an
>> "external" script...
>>
>> Therefore, first you have to know the requirements...
>>
>> Denis
>>
>>   
>>     
> see "check_postgres" for nagios-style monitoring
> see \o /tmp/alert_data_file
> and \! mailx -s "alert" me@work.com /tmp/alert_data_file
>
>   



From jan.verheyden@uz.kuleuven.ac.be Wed Aug  12 14:45:17 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 9D4156349E7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 11:45:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 20191-07-3 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 12 Aug 2009 14:44:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx2.uzleuven.be (mx2-int.uzleuven.be [134.58.179.12])
	by mail.postgresql.org (Postfix) with ESMTP id A7D8C6348AD
	for <pgsql-sql@postgresql.org>; Wed, 12 Aug 2009 11:44:44 -0300 (ADT)
X-AuditID: 863ab30b-b7b68ae00000636c-fc-4a82d55a4505
Received: from leo.uz.kuleuven.ac.be (Unknown_Domain [193.190.32.47])
	by mx2.uzleuven.be (UZLeuven Mail Server) with SMTP id
	56.57.25452.A55D28A4; Wed, 12 Aug 2009 16:44:42 +0200 (CEST)
Received: from CONDOR.uz.kuleuven.ac.be (excclu21.uz.kuleuven.ac.be
	[172.22.3.45])
	by leo.uz.kuleuven.ac.be (8.12.8/8.12.8) with ESMTP id n7CEig54025151;
	Wed, 12 Aug 2009 16:44:42 +0200
Received: from ex12-ch1-e.uz.kuleuven.ac.be ([172.22.3.107]) by
	CONDOR.uz.kuleuven.ac.be with Microsoft SMTPSVC(6.0.3790.1830);
	Wed, 12 Aug 2009 16:44:42 +0200
Received: from EX2007-MBX-2.uz.kuleuven.ac.be ([172.22.12.40]) by
	ex12-ch1-e.uz.kuleuven.ac.be ([172.22.3.107]) with mapi;
	Wed, 12 Aug 2009 16:44:41 +0200
From: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
To: "'Rob Sargent'" <robjsargent@gmail.com>
CC: "'Denis BUCHER'" <dbucherml@hsolutions.ch>, "'pgsql-sql@postgresql.org'"
	<pgsql-sql@postgresql.org>
Date: Wed, 12 Aug 2009 16:44:41 +0200
Subject: Re: mail alert
Thread-Topic: [SQL] mail alert
Thread-Index: AcobVV1L/fqp3YY5RqeCI9+T81lQCQABbCTA
Message-ID: 
 <E30C7040DE22624185BAD4093190B54437BE5DB4AF@EX2007-MBX-2.uz.kuleuven.ac.be>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A81F244.9070402@hsolutions.ch> <4A821D12.3050500@gmail.com>
	<E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A82CB26.3070208@gmail.com>
In-Reply-To: <4A82CB26.3070208@gmail.com>
Accept-Language: nl-NL, en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: nl-NL, en-US
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
MIME-Version: 1.0
X-OriginalArrivalTime: 12 Aug 2009 14:44:42.0281 (UTC)
	FILETIME=[6D46C990:01CA1B5B]
X-Scanned-By: MIMEDefang 2.48 on 193.190.32.47
X-UZLTracking: ksdfkKHKYKPjnqsdKhlfd
X-Brightmail-Tracker: AAAAAQAAAZE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.253 tagged_above=-10 required=5 tests=AWL=1.346,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/65
X-Sequence-Number: 32974

SGksDQoNCklzIGl0IHBvc3NpYmxlIHRvIHVzZSBwZ3NxbCBmb3IgY3JlYXRpbmcgYSBtYWlsZXIg
c2NyaXB0Pz8NCkNhbiB5b3UgaGVscCBtZSBhcyB3ZWxsIHdpdGggbXkgZmlyc3QgcXVlc3Rpb24/
IChiZWxvdykNCg0KVGhhbmtzIGEgbG90IQ0KDQpKYW4NCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdl
LS0tLS0NCkZyb206IFJvYiBTYXJnZW50IFttYWlsdG86cm9ianNhcmdlbnRAZ21haWwuY29tXSAN
ClNlbnQ6IFdlZG5lc2RheSwgQXVndXN0IDEyLCAyMDA5IDQ6MDEgUE0NClRvOiBKYW4gVmVyaGV5
ZGVuDQpDYzogJ0RlbmlzIEJVQ0hFUic7ICdwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcnDQpTdWJq
ZWN0OiBSZTogW1NRTF0gbWFpbCBhbGVydA0KDQpJdCBsb29rcyB0byBtZSBsaWtlIHlvdSB3YW50
IERlbm5pcydzICMyLiAgTGF1Y2ggYSBtYWlsZXIgc2NyaXB0IGZyb20gYSANCnRyaWdnZXIgZnVu
Y3Rpb24uICAoRnVuY3Rpb24gY2FuIGJlIGFueSBsYW5ndWFnZSB5b3UncmUgZmFtaWxpYXIgd2l0
aCANCmluY2x1ZGluZyBwZ3NxbCBpZiB5b3Ugd2lzaCB0byB0YWtlIGFkdmFudGFnZSBvZg0KICAg
ICJcISB5b3VyLW1haWwtc2NyaXB0Ig0KDQpKYW4gVmVyaGV5ZGVuIHdyb3RlOg0KPiBIaSwNCj4N
Cj4gSSBnb3QgYSBiaXQgc3R1Y2suLi4gDQo+IEkgd2FzIGxvb2tpbmcgZm9yIGEgc29sdXRpb24g
Zm9yIG9wdGlvbiBhKQ0KPg0KPiBNYXliZSBJJ2xsIGZpcnN0IGV4cGxhaW4gdGhlIHNpdHVhdGlv
biBhIGJpdCBtb3JlOg0KPg0KPiBJIGhhdmUgb25lIGRhdGFiYXNlIGZvciBwYXRpZW50IHJlZ2lz
dHJhdGlvbg0KPiBBbm90aGVyIG9uZSBmb3IgaW1hZ2Ugc3RvcmFnZQ0KPiBBbmQgYSB0aGlyZCBv
bmUgZm9yIGJvb2sga2VlcGluZw0KPiBBIHBhdGllbnQgc2hvdWxkIGJlIHJlZ2lzdGVyZWQgZmly
c3QgYmVmb3JlIHRoZSBpbWFnZXMgYXJlIHN0b3JlZCwgc28gaWYgdGhlcmUgaXMgYSBuZXcgbGlu
ZSBpbiB0aGUgc2Vjb25kIGRhdGFiYXNlIHdpdGggYW4gaWQgd2hpY2ggZG9lcyBub3QgZXhpc3Qg
eWV0LCBpdCBoYXMgdG8gYmUgbm90aWZpZWQgaW4gdGhlIGJvb2sga2VlcGluZyBkYXRhYmFzZS4N
Cj4NCj4gTm93IHRoZSBxdWVzdGlvbnM6DQo+IAkxKSBDYW4gSSBkbyB0aGlzIHdpdGggdGhlIGlu
bmVyIGpvaW4gKHRhYmxlcyBzdWJqZWN0X2lkIGZyb20gREIxLCBwYXRfaWQgZnJvbSBEQjIpLCB0
aGVyZSBpdCBpcyB0d28gZGlmZmVyZW50IGRhdGFiYXNlcyANCj4gCTIpIE9uY2UgaXQgaXMgbm90
aWZpZWQgaW4gdGhlIGJvb2sga2VlcGluZyB0aGF0IGlzIG5vdCByZWdpc3RlcmVkIHlldCwgaXMg
aXQgYmVzdCB0byBwb2xsIG9uIHRoaXMgY29sdW1uIHRvIHNlbmQgYSB3YXJuaW5nLCBvciB1c2Ug
YSB0cmlnZ2VyPz8NCj4NCj4gVGhhbmtzISENCj4NCj4NCj4gSmFuDQo+IC0tLS0tT3JpZ2luYWwg
TWVzc2FnZS0tLS0tDQo+IEZyb206IFJvYiBTYXJnZW50IFttYWlsdG86cm9ianNhcmdlbnRAZ21h
aWwuY29tXSANCj4gU2VudDogV2VkbmVzZGF5LCBBdWd1c3QgMTIsIDIwMDkgMzozOCBBTQ0KPiBU
bzogRGVuaXMgQlVDSEVSDQo+IENjOiBKYW4gVmVyaGV5ZGVuOyAncGdzcWwtc3FsQHBvc3RncmVz
cWwub3JnJw0KPiBTdWJqZWN0OiBSZTogW1NRTF0gbWFpbCBhbGVydA0KPg0KPiBEZW5pcyBCVUNI
RVIgd3JvdGU6DQo+ICAgDQo+PiBIZWxsbywNCj4+DQo+PiBKYW4gVmVyaGV5ZGVuIGEgw6ljcml0
IDoNCj4+ICAgDQo+PiAgICAgDQo+Pj4gSSB3YXMgbG9va2luZyBpbiB3aGF0IHdheSBpdOKAmXMg
cG9zc2libGUgdG8gYWxlcnQgdmlhIG1haWwgd2hlbiBzb21lDQo+Pj4gY29uZGl0aW9ucyBhcmUg
dHJ1ZSBpbiBhIGRhdGFiYXNlLg0KPj4+ICAgICANCj4+PiAgICAgICANCj4+IGEpIElmIHRoZSBh
bGVydCBpcyBub3QgInZlcnkgdXJnZW50IiBpLmUuIHlvdSBjYW4gYWx0ZXIgc29tZSBtaW51dGVz
DQo+PiBsYXRlciBJIHdvdWxkIGRvIGl0IGxpa2UgdGhpcyA6DQo+Pg0KPj4gMS4gQ3JlYXRlIGEg
ZnVuY3Rpb24gdGhhdCByZXR1cm5zIHdoYXQgeW91IG5lZWQsIG1vc3QgaW1wb3J0YW50bHkgaWYg
dGhlDQo+PiBjb25kaXRpb25zIGFyZSBtZXQNCj4+DQo+PiAyLiBDcmVhdGUgYSBzY3JpcHQgdGhh
dCBkb2VzIHNvbWV0aGluZyBsaWtlICJTRUxFQ1QgKiBGUk9NIGZ1bmN0aW9uKCkuLi4iDQo+Pg0K
Pj4gYikgSWYgdGhlIGVtYWlsIHNob3VsZCBiZSBzZW50IGltbWVkaWF0ZWx5LCB5b3UgY291bGQg
Y3JlYXRlIGEgcGVybA0KPj4gZnVuY3Rpb24gaW4gdGhlIGRhdGFiYXNlLCBsYXVuY2hlZCBieSBh
IFRSSUdHRVIgYW5kIGxhdW5jaGluZyBhbg0KPj4gImV4dGVybmFsIiBzY3JpcHQuLi4NCj4+DQo+
PiBUaGVyZWZvcmUsIGZpcnN0IHlvdSBoYXZlIHRvIGtub3cgdGhlIHJlcXVpcmVtZW50cy4uLg0K
Pj4NCj4+IERlbmlzDQo+Pg0KPj4gICANCj4+ICAgICANCj4gc2VlICJjaGVja19wb3N0Z3JlcyIg
Zm9yIG5hZ2lvcy1zdHlsZSBtb25pdG9yaW5nDQo+IHNlZSBcbyAvdG1wL2FsZXJ0X2RhdGFfZmls
ZQ0KPiBhbmQgXCEgbWFpbHggLXMgImFsZXJ0IiBtZUB3b3JrLmNvbSAvdG1wL2FsZXJ0X2RhdGFf
ZmlsZQ0KPg0KPiAgIA0K



From jasen@xnet.co.nz Thu Aug  13 21:23:55 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 771706359A9
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 18:23:55 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 47100-01-7 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 21:23:51 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from titania.wxnz.net (titania.wxnz.net [58.28.4.13])
	by mail.postgresql.org (Postfix) with ESMTP id C6111634BBF
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 18:23:12 -0300 (ADT)
Received: from gonzo (ip-118-90-139-27.xdsl.xnet.co.nz [118.90.139.27])
	by titania.wxnz.net (Postfix) with ESMTP id 833851098173
	for <pgsql-sql@postgresql.org>; Fri, 14 Aug 2009 09:23:10 +1200 (NZST)
Received: from news by gonzo with local (Exim 4.69)
	(envelope-from <news@reversiblemaps.ath.cx>) id 1MbZ6X-0004bP-IS
	for pgsql-sql@postgresql.org; Fri, 14 Aug 2009 00:08:25 +1200
To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: mail alert
Date: 13 Aug 2009 12:08:25 GMT
Organization: Dis (not Dat) Organisation
Lines: 26
Message-ID: <h60vnp$h8t$1@reversiblemaps.ath.cx>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: reversiblemaps.ath.cx 1250165305 17693 127.0.0.1 (13 Aug 2009
	12:08:25 GMT)
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a
	+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;
	(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6
	{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;
	8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-2 (Linux)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.116 tagged_above=-10 required=5
	tests=BAYES_40=-0.185, DATE_IN_PAST_06_12=1.069, RCVD_IN_DNSWL_LOW=-1
X-Spam-Level: 
X-Archive-Number: 200908/68
X-Sequence-Number: 32977


On 2009-08-11, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote:
>
> Hi All,
>
> I was looking in what way it's possible to alert via mail when some conditi=
> ons are true in a database.
>
> Thanks in advance!

Assuming you mean email, and not ink on paper (hmm, OTOH you could load
postcards into a printer....)

you could do this using NOTIFY and a listener written in some other
langauge,  notify is really neat.

or possibly invoke mail(1) with a plpythonu or C function,

or as superuser you can write a file (using copy ...) and arrange for something else to
look, find it, and mail it.

arbitrary file contents are possible with copy: 

COPY (SELECT NULL) TO myfile_name WITH NULL AS myfile_contents;





From jasen@xnet.co.nz Thu Aug  13 21:23:55 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7C27F635825
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 18:23:55 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 46845-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 21:23:52 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from titania.wxnz.net (titania.wxnz.net [58.28.4.13])
	by mail.postgresql.org (Postfix) with ESMTP id C2D7C634AC5
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 18:23:12 -0300 (ADT)
Received: from gonzo (ip-118-90-139-27.xdsl.xnet.co.nz [118.90.139.27])
	by titania.wxnz.net (Postfix) with ESMTP id 622A91098125
	for <pgsql-sql@postgresql.org>; Fri, 14 Aug 2009 09:23:09 +1200 (NZST)
Received: from news by gonzo with local (Exim 4.69)
	(envelope-from <news@reversiblemaps.ath.cx>) id 1MbZED-0004dh-9e
	for pgsql-sql@postgresql.org; Fri, 14 Aug 2009 00:16:21 +1200
To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: mail alert
Date: 13 Aug 2009 12:16:21 GMT
Organization: Dis (not Dat) Organisation
Lines: 11
Message-ID: <h6106l$h8t$2@reversiblemaps.ath.cx>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: reversiblemaps.ath.cx 1250165781 17693 127.0.0.1 (13 Aug 2009
	12:16:21 GMT)
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a
	+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;
	(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6
	{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;
	8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-2 (Linux)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.671 tagged_above=-10 required=5
	tests=BAYES_20=-0.74, DATE_IN_PAST_06_12=1.069, RCVD_IN_DNSWL_LOW=-1
X-Spam-Level: 
X-Archive-Number: 200908/69
X-Sequence-Number: 32978

On 2009-08-12, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote:
> --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> It's on Windows
>

I'd go with notify and a listener written in C using c-client to send
emails, but only because I've used those before.




From alvherre@commandprompt.com Thu Aug  13 21:54:36 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id A382662DB8E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 18:54:36 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 30567-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 21:54:20 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lists.commandprompt.com (host-159.commandprompt.net
	[207.173.203.159])
	by mail.postgresql.org (Postfix) with ESMTP id CD61D63373A
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 18:54:25 -0300 (ADT)
Received: from perhan.alvh.no-ip.org ([201.220.118.158]) (authenticated
 bits=0)
	by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id n7DLsN57028189
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 13 Aug 2009 14:54:26 -0700
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id 308AFD41A4; Thu, 13 Aug 2009 17:54:20 -0400 (CLT)
Date: Thu, 13 Aug 2009 17:54:20 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jasen Betts <jasen@xnet.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: mail alert
Message-ID: <20090813215420.GU5909@alvh.no-ip.org>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
	<h6106l$h8t$2@reversiblemaps.ath.cx>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <h6106l$h8t$2@reversiblemaps.ath.cx>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0
	(lists.commandprompt.com [207.173.203.159]);
	Thu, 13 Aug 2009 14:54:26 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.904 tagged_above=-10 required=5 tests=AWL=0.695,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/70
X-Sequence-Number: 32979

Jasen Betts wrote:
> On 2009-08-12, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote:
> > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_
> > Content-Type: text/plain; charset="us-ascii"
> > Content-Transfer-Encoding: quoted-printable
> >
> > It's on Windows
> >
> 
> I'd go with notify and a listener written in C using c-client to send
> emails, but only because I've used those before.

I wouldn't write it in C but rather Perl or Python, but whatever suits
your fancy should work (Visual Basic anyone?).  The advantages to using
a listener program instead of doing it in a trigger or something like
that are:

- transaction semantics are kept; you don't send an email only to find
out your transaction has been rolled back for whatever reason, and then
send a second email when the transaction is replayed

- you don't block the database system just because your mail server is
down

- the email can be sent on whatever schedule fits the listener program

- the listener client can run elsewhere, not only in the database server

- any further external processing can take place at that time, without
bothering the database server

- other stuff I don't recall ATM

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



From tim@tim-landscheidt.de Thu Aug  13 22:24:26 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CE2E2635688
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 19:24:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 71961-06-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 22:24:23 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by mail.postgresql.org (Postfix) with ESMTP id 0F752635441
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 19:24:23 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1MbiiY-0001rz-RI
	for pgsql-sql@postgresql.org; Thu, 13 Aug 2009 22:24:18 +0000
Received: from e177123205.adsl.alicedsl.de ([85.177.123.205])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 22:24:18 +0000
Received: from tim by e177123205.adsl.alicedsl.de with local (Gmexim 0.1
	(Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 22:24:18 +0000
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: mail alert
Date: Thu, 13 Aug 2009 22:24:07 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 38
Message-ID: <m3bpmjfj3s.fsf@passepartout.tim-landscheidt.de>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
	<h6106l$h8t$2@reversiblemaps.ath.cx>
	<20090813215420.GU5909@alvh.no-ip.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@ger.gmane.org
X-Gmane-NNTP-Posting-Host: e177123205.adsl.alicedsl.de
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:EaCg4d9Hcc1/WFx6NcoFaVGVBmY=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-3.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, RCVD_IN_DNSWL_LOW=-1
X-Spam-Level: 
X-Archive-Number: 200908/71
X-Sequence-Number: 32980

Alvaro Herrera <alvherre@commandprompt.com> wrote:

>> > It's on Windows

>> I'd go with notify and a listener written in C using c-client to send
>> emails, but only because I've used those before.

> I wouldn't write it in C but rather Perl or Python, but whatever suits
> your fancy should work (Visual Basic anyone?).  The advantages to using
> a listener program instead of doing it in a trigger or something like
> that are:

> - transaction semantics are kept; you don't send an email only to find
> out your transaction has been rolled back for whatever reason, and then
> send a second email when the transaction is replayed

> - you don't block the database system just because your mail server is
> down

> - the email can be sent on whatever schedule fits the listener program

> - the listener client can run elsewhere, not only in the database server

> - any further external processing can take place at that time, without
> bothering the database server

> - other stuff I don't recall ATM

The main disadvantage in using a listener is that it is your
responsibility to make sure that the listener is listening
24/7 - from before the database accepts other connections,
through network failures, bugs, etc. - otherwise notifica-
tions will be lost. Therefore I find it much more reliable
(and easier to program) to copy the relevant data to a table
"mailqueue" (or whatever) and then process that queue every
other minute.

Tim




From alvherre@commandprompt.com Thu Aug  13 22:36:37 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 1A07763405D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 19:36:37 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 38729-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 22:36:20 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lists.commandprompt.com (host-159.commandprompt.net
	[207.173.203.159])
	by mail.postgresql.org (Postfix) with ESMTP id 5831A63356E
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 19:36:26 -0300 (ADT)
Received: from perhan.alvh.no-ip.org ([201.220.118.158]) (authenticated
 bits=0)
	by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id n7DMaOBt031447
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 13 Aug 2009 15:36:27 -0700
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id BB6DBD41A4; Thu, 13 Aug 2009 18:36:00 -0400 (CLT)
Date: Thu, 13 Aug 2009 18:36:00 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Tim Landscheidt <tim@tim-landscheidt.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: mail alert
Message-ID: <20090813223600.GW5909@alvh.no-ip.org>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
	<h6106l$h8t$2@reversiblemaps.ath.cx>
	<20090813215420.GU5909@alvh.no-ip.org>
	<m3bpmjfj3s.fsf@passepartout.tim-landscheidt.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <m3bpmjfj3s.fsf@passepartout.tim-landscheidt.de>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0
	(lists.commandprompt.com [207.173.203.159]);
	Thu, 13 Aug 2009 15:36:27 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.911 tagged_above=-10 required=5 tests=AWL=0.688,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/72
X-Sequence-Number: 32981

Tim Landscheidt wrote:

> The main disadvantage in using a listener is that it is your
> responsibility to make sure that the listener is listening
> 24/7 - from before the database accepts other connections,
> through network failures, bugs, etc. - otherwise notifica-
> tions will be lost. Therefore I find it much more reliable
> (and easier to program) to copy the relevant data to a table
> "mailqueue" (or whatever) and then process that queue every
> other minute.

You just have to make sure the listener scans the table for possible
events that were missed.  Think of notifications as signals to wake up
and check for possible work, not data carriers.  The mailqueue table
should still be there for the data.  The only difference between your
approach and mine is that you poll every minute instead of sleeping
until getting a notification.  If your system is going to be receiving
notifications fairly frequently, it is probably better to stay with
polling.  (This is what Skype's replication system does, and Hannu
Krossing says "what, are you going to optimize for the time when the
server is idle?")

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



From aklaver@comcast.net Thu Aug  13 23:30:55 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id BBC4F633132
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 20:30:55 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 48266-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 13 Aug 2009 23:30:38 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from QMTA13.emeryville.ca.mail.comcast.net
	(qmta13.emeryville.ca.mail.comcast.net [76.96.27.243])
	by mail.postgresql.org (Postfix) with ESMTP id 74754633F76
	for <pgsql-sql@postgresql.org>; Thu, 13 Aug 2009 20:30:44 -0300 (ADT)
Received: from OMTA04.emeryville.ca.mail.comcast.net ([76.96.30.35])
	by QMTA13.emeryville.ca.mail.comcast.net with comcast
	id TqXc1c00D0lTkoCADzWjFE; Thu, 13 Aug 2009 23:30:43 +0000
Received: from tucker.local ([24.17.164.54])
	by OMTA04.emeryville.ca.mail.comcast.net with comcast
	id TzWi1c0011Ak4z48QzWi0N; Thu, 13 Aug 2009 23:30:42 +0000
From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: mail alert
Date: Thu, 13 Aug 2009 16:30:41 -0700
User-Agent: KMail/1.9.10
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<4A821D12.3050500@gmail.com>
	<E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
In-Reply-To: 
 <E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200908131630.41508.aklaver@comcast.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.247 tagged_above=-10 required=5 tests=AWL=1.352,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/73
X-Sequence-Number: 32982

On Wednesday 12 August 2009 6:27:25 am Jan Verheyden wrote:
> Hi,
>
> I got a bit stuck...
> I was looking for a solution for option a)
>
> Maybe I'll first explain the situation a bit more:
>
> I have one database for patient registration
> Another one for image storage
> And a third one for book keeping
> A patient should be registered first before the images are stored, so if
> there is a new line in the second database with an id which does not exist
> yet, it has to be notified in the book keeping database.
>
> Now the questions:
> 	1) Can I do this with the inner join (tables subject_id from DB1, pat_id
> from DB2), there it is two different databases 2) Once it is notified in
> the book keeping that is not registered yet, is it best to poll on this
> column to send a warning, or use a trigger??
>
> Thanks!!
>

If at all possible, try to move all that information into schema's of one 
database. As it stands now you have a lot of moving parts to keep track of via 
external processes. It is possible but you lose transactional support and trust 
me that turns into a royal pain.


-- 
Adrian Klaver
aklaver@comcast.net



From cbbrowne@ca.afilias.info Fri Aug  14 15:21:20 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 19986632AE4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 14 Aug 2009 12:21:20 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 78922-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 14 Aug 2009 15:21:09 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by mail.postgresql.org (Postfix) with ESMTP id 6040C63266B
	for <pgsql-sql@postgresql.org>; Fri, 14 Aug 2009 12:21:09 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.14.3/8.14.3) with ESMTP id n7EFL8HX030381
	for <pgsql-sql@postgresql.org>; Fri, 14 Aug 2009 12:21:08 -0300 (ADT)
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.14.3/8.14.3/Submit) id n7EFITkr026449
	for pgsql-sql@postgresql.org; Fri, 14 Aug 2009 12:18:29 -0300 (ADT)
	(envelope-from news)
From: Christopher Browne <cbbrowne@ca.afilias.info>
X-Newsgroups: pgsql.sql
Subject: Re: mail alert
Date: Fri, 14 Aug 2009 11:18:21 -0400
Organization: Hub.Org Networking Services
Lines: 74
Message-ID: <87ab22qv9e.fsf@dba2.int.libertyrms.com>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
	<h6106l$h8t$2@reversiblemaps.ath.cx>
	<20090813215420.GU5909@alvh.no-ip.org>
	<m3bpmjfj3s.fsf@passepartout.tim-landscheidt.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@news.hub.org
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:nv1bmq5sPE/HjV2lwY0H8m7gaGg=
To: pgsql-sql@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 200908/80
X-Sequence-Number: 32989

tim@tim-landscheidt.de (Tim Landscheidt) writes:
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
>>> > It's on Windows
>
>>> I'd go with notify and a listener written in C using c-client to send
>>> emails, but only because I've used those before.
>
>> I wouldn't write it in C but rather Perl or Python, but whatever suits
>> your fancy should work (Visual Basic anyone?).  The advantages to using
>> a listener program instead of doing it in a trigger or something like
>> that are:
>
>> - transaction semantics are kept; you don't send an email only to find
>> out your transaction has been rolled back for whatever reason, and then
>> send a second email when the transaction is replayed
>
>> - you don't block the database system just because your mail server is
>> down
>
>> - the email can be sent on whatever schedule fits the listener program
>
>> - the listener client can run elsewhere, not only in the database server
>
>> - any further external processing can take place at that time, without
>> bothering the database server
>
>> - other stuff I don't recall ATM
>
> The main disadvantage in using a listener is that it is your
> responsibility to make sure that the listener is listening
> 24/7 - from before the database accepts other connections,
> through network failures, bugs, etc. - otherwise notifica-
> tions will be lost. Therefore I find it much more reliable
> (and easier to program) to copy the relevant data to a table
> "mailqueue" (or whatever) and then process that queue every
> other minute.

Actually, I don't think there's any real disagreement here...

 - The *important* bit is to make sure that the data required to
   generate the email is queued in the database.

 - Whether you poll or use notify/listen is *way* less important.

You could implement the "listener process" a number of ways:

  - It could be a "cron" that wakes up every so often
    to do whatever work is outstanding

  - It could be a "polling daemon" that sleeps for a while between
    iterations.

    That seems a little nicer than the "cron" approach in that it
    eliminates a troublesome scenario, namely the case where there's a
    lot of work to do (flooded queue?)  so that processing takes longer
    than the polling interval, leading to the risk that a second "cron"
    starts up while the previous one is still working.

  - It could be a "listening daemon" that listens for notifications to
    indicate that work is outstanding

    That is a little better than the "polling daemon" in that it doesn't
    need to wait the full polling period to start processing new work.

Any of those three approaches are quite viable, as long as you're
careful to cover scenarios like:
 - daemon falling over
 - accidentally starting multiple "queue processors"
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"