[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:image001.png@01CA1C1B.03410D10]
Anyone an idea?
Regards,
Jan
<>
Re: [SQL] simple? query
> From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jan Verheyden
> Subject: [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;
Perhaps UPDATE is what you're looking for?
http://www.postgresql.org/docs/8.4/static/sql-update.html
UPDATE test SET registered = '1' WHERE uid = 'janvleuven10';
UPDATE test set registered = '0' WHERE uid <> 'janvleuven10';
Mike
--
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
On 2009-08-11, Jan Verheyden wrote: > > Hi All, > > I was looking in what way it's possible to alert via mail when some conditi= > ons are true in a database. > > Thanks in advance! Assuming you mean email, and not ink on paper (hmm, OTOH you could load postcards into a printer) you could do this using NOTIFY and a listener written in some other langauge, notify is really neat. or possibly invoke mail(1) with a plpythonu or C function, or as superuser you can write a file (using copy ...) and arrange for something else to look, find it, and mail it. arbitrary file contents are possible with copy: COPY (SELECT NULL) TO myfile_name WITH NULL AS myfile_contents; -- 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
On 2009-08-12, Jan Verheyden wrote: > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > It's on Windows > I'd go with notify and a listener written in C using c-client to send emails, but only because I've used those before. -- 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
Jasen Betts wrote: > On 2009-08-12, Jan Verheyden wrote: > > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_ > > Content-Type: text/plain; charset="us-ascii" > > Content-Transfer-Encoding: quoted-printable > > > > It's on Windows > > > > I'd go with notify and a listener written in C using c-client to send > emails, but only because I've used those before. I wouldn't write it in C but rather Perl or Python, but whatever suits your fancy should work (Visual Basic anyone?). The advantages to using a listener program instead of doing it in a trigger or something like that are: - transaction semantics are kept; you don't send an email only to find out your transaction has been rolled back for whatever reason, and then send a second email when the transaction is replayed - you don't block the database system just because your mail server is down - the email can be sent on whatever schedule fits the listener program - the listener client can run elsewhere, not only in the database server - any further external processing can take place at that time, without bothering the database server - other stuff I don't recall ATM -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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
Alvaro Herrera wrote: >> > It's on Windows >> I'd go with notify and a listener written in C using c-client to send >> emails, but only because I've used those before. > I wouldn't write it in C but rather Perl or Python, but whatever suits > your fancy should work (Visual Basic anyone?). The advantages to using > a listener program instead of doing it in a trigger or something like > that are: > - transaction semantics are kept; you don't send an email only to find > out your transaction has been rolled back for whatever reason, and then > send a second email when the transaction is replayed > - you don't block the database system just because your mail server is > down > - the email can be sent on whatever schedule fits the listener program > - the listener client can run elsewhere, not only in the database server > - any further external processing can take place at that time, without > bothering the database server > - other stuff I don't recall ATM The main disadvantage in using a listener is that it is your responsibility to make sure that the listener is listening 24/7 - from before the database accepts other connections, through network failures, bugs, etc. - otherwise notifica- tions will be lost. Therefore I find it much more reliable (and easier to program) to copy the relevant data to a table "mailqueue" (or whatever) and then process that queue every other minute. Tim -- 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
Tim Landscheidt wrote: > The main disadvantage in using a listener is that it is your > responsibility to make sure that the listener is listening > 24/7 - from before the database accepts other connections, > through network failures, bugs, etc. - otherwise notifica- > tions will be lost. Therefore I find it much more reliable > (and easier to program) to copy the relevant data to a table > "mailqueue" (or whatever) and then process that queue every > other minute. You just have to make sure the listener scans the table for possible events that were missed. Think of notifications as signals to wake up and check for possible work, not data carriers. The mailqueue table should still be there for the data. The only difference between your approach and mine is that you poll every minute instead of sleeping until getting a notification. If your system is going to be receiving notifications fairly frequently, it is probably better to stay with polling. (This is what Skype's replication system does, and Hannu Krossing says "what, are you going to optimize for the time when the server is idle?") -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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
On Wednesday 12 August 2009 6:27:25 am 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!! > If at all possible, try to move all that information into schema's of one database. As it stands now you have a lot of moving parts to keep track of via external processes. It is possible but you lose transactional support and trust me that turns into a royal pain. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
