Re: [SQL] mail alert
It's on Windows From: ramasubramanian [mailto:ramasubramania...@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(linux/or windows or..)? - Original Message - From: Jan Verheydenmailto:jan.verhey...@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 conditions are true in a database. Thanks in advance! Jan
Re: [SQL] mail alert
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:robjsarg...@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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
Hi, Is it possible to use pgsql for creating a mailer script?? Can you help me as well with my first question? (below) Thanks a lot! Jan -Original Message- From: Rob Sargent [mailto:robjsarg...@gmail.com] Sent: Wednesday, August 12, 2009 4:01 PM To: Jan Verheyden Cc: 'Denis BUCHER'; 'pgsql-sql@postgresql.org' Subject: Re: [SQL] mail alert 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:robjsarg...@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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] mail alert
Hi All, I was looking in what way it's possible to alert via mail when some conditions are true in a database. Thanks in advance! Jan
[SQL] trigger problem
Hi,
If I try this to run in a trigger function
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'insert into test (uid) values (' ||
quote_literal(NEW.pat_id) || ')');
return new;
perform dblink_disconnect('myconnect');'
I get the message
'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement SELECT dblink_connect('myconnect','dbname=postgres
password=uzleuven')
PL/pgSQL function test_update_trigger line 2 at perform'
This happens only in one of my two databases, anyone an idea?
Regards,
Jan
[SQL] FW: trigger problem
Hi,
I keep looking for myself and tried as well the following code:
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
create view remote as
select *
from dblink('myconnect','select
uid from test')
as t1(pat_id text);
perform * from remote where pat_id like '|| query_literal(NEW.pat_id) ||';
return new;
perform dblink_disconnect('myconnect');'
And again I get the same error message as below..
Is this because it tries to make connection for each row in the column??
Thanks,
Jan
From: Jan Verheyden
Sent: Thursday, August 06, 2009 11:09 AM
To: 'pgsql-sql@postgresql.org'
Subject: trigger problem
Hi,
If I try this to run in a trigger function
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'update test set uploaded = 1 where uid =' ||
quote_literal(NEW.pat_id) || ' ');
return new;
perform dblink_disconnect('myconnect');'
I get the message
'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement SELECT dblink_connect('myconnect','dbname=postgres
password=uzleuven')
PL/pgSQL function test_update_trigger line 2 at perform'
This happens only in one of my two databases, anyone an idea?
Regards,
Jan
[SQL] question about pgagent
Hi All,
First I will explain what I try to do:
I'd like to synchronize more or less two databases, in a third database. I
mean, I want to book keep when an object has arrived in both databases. (In one
database I am allowed to use trigger function but not in the other)
1) Postgres (maintenance database)
2) Database1
3) Database3
How can I set up in a cron job (pgagent), that I check if a certain value f.e.
id is present in both database1 database2 and then flag this in the maintenance
database?
Select_connect('myconnect', 'dbase=database1');
Select_connect('myconnect2','dbase=database2');
Using
select dblink('myconnect',
Thanks in advance!
Jan
