From: Rob Sargent <robjsargent@gmail.com>
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
Cc: Denis BUCHER <dbucherml@hsolutions.ch>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: mail alert
Date: Wed, 12 Aug 2009 08:01:10 -0600
Message-ID: <4A82CB26.3070208@gmail.com> (raw)
In-Reply-To: <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>
<E30C7040DE22624185BAD4093190B54437BE5DB4AE@EX2007-MBX-2.uz.kuleuven.ac.be>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, jan.verheyden@uz.kuleuven.ac.be, dbucherml@hsolutions.ch
Subject: Re: mail alert
In-Reply-To: <4A82CB26.3070208@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox