Re: [SQL] mail alert

[Rob Sargent]

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

[Ray Stell]

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

[Thomas Kellerer]

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

[Greg Stark]

On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellerer 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  (Or if you have an index
on  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

[Jorge Godoy]

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 


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" 
> To: 
> 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

[Jan Verheyden]

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 Verheyden
To: '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

[Xharon]

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

[Jan Verheyden]

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

[Jan Verheyden]

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