Re: [SQL] mail alert
- Original Message - *From:* Jan Verheyden jan.verhey...@uz.kuleuven.ac.be *To:* 'pgsql-sql@postgresql.org' %27pgsql-...@postgresql.org%27 *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. 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. -- Shoaib Mir http://shoaibmir.wordpress.com/
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] mysql code questions
http://www.brentozar.com/archive/2009/04/getting-the-most-recent-record/ How this works? What is ttNewer? What is a clustered primary key in mysql? This is as good as I can do to get this into pg: create table TestTable ( id int not null, create_date date not null, info1 VARCHAR(50) NOT NULL, info2 VARCHAR(50) NOT NULL, constraint PK_TestTable PRIMARY KEY(id,create_date) ); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (1, '1/1/09', 'Blue', 'Green'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (1, '1/2/09', 'Red', 'Yellow'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (1, '1/3/09', 'Orange', 'Purple'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (2, '1/1/09', 'Yellow', 'Blue'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (2, '1/5/09', 'Blue', 'Orange'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (3, '1/2/09', 'Green', 'Purple'); INSERT INTO TestTable (id, create_date, info1, info2) VALUES (3, '1/8/09', 'Red', 'Blue'); select tt.* FROM TestTable tt LEFT OUTER JOIN TestTable ttNewer ON tt.id = ttNewer.id AND tt.create_date ttNewer.create_date WHERE ttNewer.id IS NULL; id | create_date | info1 | info2 +-++ 1 | 2009-01-03 | Orange | Purple 2 | 2009-01-05 | Blue | Orange 3 | 2009-01-08 | Red| Blue (3 rows) -- 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] mysql code questions
Ray Stell wrote on 12.08.2009 20:19: http://www.brentozar.com/archive/2009/04/getting-the-most-recent-record/ How this works? What is ttNewer? What is a clustered primary key in mysql? That article talks about SQL Server not MySQL. select tt.* FROM TestTable tt LEFT OUTER JOIN TestTable ttNewer ON tt.id = ttNewer.id AND tt.create_date ttNewer.create_date WHERE ttNewer.id IS NULL; I would probably do it this way: SELECT tt. * FROM testtable tt WHERE create_date = (SELECT MAX(create_date) FROM testtable tt2 WHERE tt.id = tt2.id); Don't know which one is more efficient (with just a few rows, it doesn't really pay off to look at the execution plan) Thomas -- 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] mysql code questions
On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellererspam_ea...@gmx.net wrote: Ray Stell wrote on 12.08.2009 20:19: I would probably do it this way: SELECT tt. * FROM testtable tt WHERE create_date = (SELECT MAX(create_date) FROM testtable tt2 WHERE tt.id = tt2.id); The article is *very* unclear about the problem. You have to read the whole schema and their proposed solution to figure out what the problem they're trying to solve is. This query doesn't solve it. They want the newest record for *each* id. Ie, the equivalent of select id,max(create_date) from tt group by id except including the other attributes for the record with that date. There are a number of ways to solve it. Postgres supports all of the solutions in the comments including the one proposed in the post as well as the window-function based solution. It also supports an extension which could be even more efficient than all of them: select DISTINCT ON (id) from tt order by id,create_date desc If you have an index on id,create_date desc (Or if you have an index on id,create_date and use order by id desc, create_date desc in the query) then this could do a single index scan. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Month/year between two dates
I think he wanted something like:
test=# create table month_test(id serial primary key, start_date date not
null, end_date date not null);
CREATE TABLE
test=# insert into month_test (start_date, end_date) values
('2009-01-01'::date, '2009-08-31'::date);
INSERT 0 1
test=# insert into month_test (start_date, end_date) values
('2009-08-01'::date, '2009-08-10'::date);
INSERT 0 1
test=# insert into month_test (start_date, end_date) values
('2009-08-11'::date, '2009-08-31'::date);
INSERT 0 1
test=# select * from month_test;
id | start_date | end_date
++
1 | 2009-01-01 | 2009-08-31
2 | 2009-08-01 | 2009-08-10
3 | 2009-08-11 | 2009-08-31
(3 rows)
test=# select * from month_test where (start_date, end_date) overlaps
('2009-05-01'::date, '2009-05-31'::date);
id | start_date | end_date
++
1 | 2009-01-01 | 2009-08-31
(1 row)
test=#
--
Jorge Godoy jgo...@gmail.com
On Wed, Aug 12, 2009 at 00:59, ramasubramanian
ramasubramania...@renaissance-it.com wrote:
Dear Bor,
How you will 1 record for 2009/05 (if you use 2009/05 ) it will fetch all
the records as it is not having month 05
am i correct?
- Original Message - From: Bor dborov...@gmail.com
To: pgsql-sql@postgresql.org
Sent: Tuesday, August 11, 2009 6:43 PM
Subject: [SQL] Month/year between two dates
Hi to all,
I have a very simple question. Let's say that I have three records (id,
date
from, date to):
1 2009-01-01 2009-08-31
2 2009-08-01 2009-08-10
3 2009-08-11 2009-08-31
Now I want to get records, related to a single month/year data (two
integers). For 2009/08 (int1 = 2009, int2 = 8) I should get all three
records, for 2009/05 only record 1, but for 2009/11 none of the records.
Is there any simple way to do this? A query would do :).
Thanks alot.
--
View this message in context:
http://www.nabble.com/Month-year-between-two-dates-tp24917400p24917400.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
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
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] Updating a specific number of rows in pl/pgsql
ctid is the physical location of the row version, so it's not static.. check http://www.postgresql.org/docs/8.2/static/ddl-system-columns.html -- 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, 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
