[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
[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: '[email protected]'
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] 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
It's on Windows From: ramasubramanian [mailto:[email protected]] Sent: Wednesday, August 12, 2009 6:01 AM To: Jan Verheyden; [email protected] 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<mailto:[email protected]> To: '[email protected]' 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:[email protected]] Sent: Wednesday, August 12, 2009 3:38 AM To: Denis BUCHER Cc: Jan Verheyden; '[email protected]' 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" [email protected] /tmp/alert_data_file -- Sent via pgsql-sql mailing list ([email protected]) 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:[email protected]] Sent: Wednesday, August 12, 2009 4:01 PM To: Jan Verheyden Cc: 'Denis BUCHER'; '[email protected]' 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:[email protected]] > Sent: Wednesday, August 12, 2009 3:38 AM > To: Denis BUCHER > Cc: Jan Verheyden; '[email protected]' > 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" [email protected] /tmp/alert_data_file > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] simple? query
Hi,
I was trying to run following query but doesn't work:
if (uid='janvleuven10') then
insert into test (registered) values ('1');
else
insert into test (registered) values ('0');
end if;
[cid:[email protected]]
Anyone an idea?
Regards,
Jan
<>
FW: [SQL] simple? query
Hi, Thanks for the suggestion, the only problem is, if primary key is used then each row should be unique what is not true; since I have a column 'registered' what only can be 1 or 0... Regards, Jan -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Tim Landscheidt Sent: Friday, August 14, 2009 4:53 PM To: [email protected] Subject: Re: [SQL] simple? query "Relyea, Mike" wrote: >> The goal is, where uid not equals to 'janvleuven10' a new >> record should be inserted with the uid, and registered=0 > So if a record is found you want to update it and if a record isn't > found you want to insert it. I think you'll probably want to use > plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some > other language like Jasen suggested. I don't know of a way to do this > with straight sql. Something along the lines of: | UPDATE table SET attribute = 'something' WHERE primary_key = 'id'; | INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table); should achieve that. Tim -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
