Re: [SQL] simple? query
On 2009-08-13, Jan Verheyden wrote:
> --_004_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: multipart/alternative;
> boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_"
>
> --_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> 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;
that's not SQL. (it could be plpgsql)
if you need to do it in SQL do this.
insert into test (registered)
values ( case when uid='janvleuven10' then '1' else '0' end );
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Field or record level encryption / decryption
Hi, Anyone know what function i can use to encrypt / decrypt field or record ? When record saved, it saved in encrypt format. When i need to read data, i just call decrypt function. I am using MS Access 2003 and Postgresql 8.3 Thanks a lot. Regards, Hengky -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple? query
> From: Relyea, Mike [mailto:[email protected]] > Sent: Thursday, August 13, 2009 10:47 PM > > > From: [email protected] > [mailto:[email protected]] 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'; > > > > > From: Jan Verheyden [mailto:[email protected]] > Sent: Friday, August 14, 2009 3:11 AM > To: Relyea, Mike > Subject: RE: [SQL] simple? query > > Hi, > > Thanks for the reply. I was thinking of that, but the problem > is that if it's not registered, the uid is not in the test database... > I think in your example all the other rows will be signed as > 'not registered, is this possible?' > > Regards, > > Jan You're right, my suggestion will not insert new records. It will only update existing ones. And yes, in my example all of the existing rows where uid is not equal to janvleuven10 will have the registered value set to 0. That's how I interpreted the example you gave in your original post. Mike -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Field or record level encryption / decryption
On Fri, Aug 14, 2009 at 05:20:58PM +0800, Hengky Lie wrote: > Hi, > > Anyone know what function i can use to encrypt / decrypt field or > record ? > > When record saved, it saved in encrypt format. When i need to read data, > i just call decrypt function. See the pgcrypto extension: http://www.postgresql.org/docs/current/static/pgcrypto.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] simple? query
> From: Jan Verheyden [mailto:[email protected]] > Sent: Friday, August 14, 2009 9:03 AM > To: Relyea, Mike > Subject: RE: [SQL] simple? query > > The goal is, where uid not equals to 'janvleuven10' a new > record should be inserted with the uid, and registered=0 > > Regards, > > Jan 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. Mike -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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
Re: [SQL] mail alert
[email protected] (Tim Landscheidt) writes: > 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. Actually, I don't think there's any real disagreement here... - The *important* bit is to make sure that the data required to generate the email is queued in the database. - Whether you poll or use notify/listen is *way* less important. You could implement the "listener process" a number of ways: - It could be a "cron" that wakes up every so often to do whatever work is outstanding - It could be a "polling daemon" that sleeps for a while between iterations. That seems a little nicer than the "cron" approach in that it eliminates a troublesome scenario, namely the case where there's a lot of work to do (flooded queue?) so that processing takes longer than the polling interval, leading to the risk that a second "cron" starts up while the previous one is still working. - It could be a "listening daemon" that listens for notifications to indicate that work is outstanding That is a little better than the "polling daemon" in that it doesn't need to wait the full polling period to start processing new work. Any of those three approaches are quite viable, as long as you're careful to cover scenarios like: - daemon falling over - accidentally starting multiple "queue processors" -- output = reverse("ofni.sailifa.ac" "@" "enworbbc") Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Field or record level encryption / decryption
[email protected] (Hengky Lie) writes: > Anyone know what function i can use to encrypt / decrypt field or > record ? > > When record saved, it saved in encrypt format. When i need to read > data, i just call decrypt function. > > I am using MS Access 2003 and Postgresql 8.3 I'd suggest referring to the fine manual... http://www.postgresql.org/docs/8.3/static/pgcrypto.html -- output = reverse("ofni.sailifa.ac" "@" "enworbbc") Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Field or record level encryption / decryption
Thanks to all who response my question. I have checked the doc, but it seems too advance for my postgresql knowledge. Other question is where can i get pgcrypto modules ? For simple record encryption that contains date field, varchar, integer and text, what encryption i can use ? As far as i know, there is no way to return MD5 result back to its original value. Is this true or not ? Thanks a lot On Aug 14, 2009, at 11:21 PM, Christopher Browne wrote: [email protected] (Hengky Lie) writes: Anyone know what function i can use to encrypt / decrypt field or record ? When record saved, it saved in encrypt format. When i need to read data, i just call decrypt function. I am using MS Access 2003 and Postgresql 8.3 I'd suggest referring to the fine manual... http://www.postgresql.org/docs/8.3/static/pgcrypto.html -- output = reverse("ofni.sailifa.ac" "@" "enworbbc") Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" -- 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
Re: [SQL] Field or record level encryption / decryption
On Sat, Aug 15, 2009 at 12:07:54AM +0800, Hengky Lie wrote: > Thanks to all who response my question. > > I have checked the doc, but it seems too advance for my postgresql > knowledge. > > Other question is where can i get pgcrypto modules ? You haven't told us how you installed PostgreSQL, but you probably did it with some operating system package. In that case, there's most likely a postgresql-contrib package (or some other similarly named package) you can install that will include pgcrypto. Within that package there should be some SQL file full of "CREATE FUNCTION" statements, which you need to run in each database where you'd like to use pgcrypto. > For simple record encryption that contains date field, varchar, integer > and text, what encryption i can use ? As far as i know, there is no way > to return MD5 result back to its original value. Is this true or not ? That's a bit beyond the scope of this mailing lists. Different methods and algorithms have different attributes, benfits, and drawbacks. I'd recommend studying the subject if you're trying to protect anything seriously. As to MD5, you're correct that the idea is you can't recreate the original value without an awful lot of computing time. But again, study the field before trying to do something serious. It's complex, and easy to get wrong. - Josh / eggyknap signature.asc Description: Digital signature
