RE: PostgresSQL DLR support

limit in delete/update statement is mysql specific, it does not work with postgres.

> -----Original Message-----
> From: Marcin Sobieszczan'ski [mailto:[email protected]]
> Sent: Thursday, January 29, 2004 6:03 PM
> To: [email protected]
> Subject: Re: PostgresSQL DLR support
> 
> 
> Alexander Malysh said the following on 2004-01-29 15:17:
> > On Thursday 29 January 2004 14:29, Martin Atukunda wrote:
> > 
> >>On Wednesday 28 January 2004 13:28, Alexander  Malysh wrote:
> >>
> >>>cvs diff -Nau
> >>
> >>something like this?
> > 
> > 
> > yep
> > 
> > but I don't believe something like this will works with pgsql:
> > "UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' LIMIT 1;"
> > 
> > or like this too:
> > "DELETE FROM %s WHERE %s='%s' AND %s='%s' LIMIT 1;"
> > 
> 
> I'm sure it does not work ;-> I have also started 
> implementing postgres 
> DLR support, but after your mail I wanted to see your implementation.
> 
> the idea to make 'LIMIT 1' in update and delete in postgres is to add 
> new column in the tabel (let's say 'id' - with type 'serial')
> 
> CREATE TABLE dlr (
> 	id serial,
> 	smsc varchar(40),
> 	...
> );
> 
> then you may do delete with subquery:
> DELETE FROM %s WHERE id IN (SELECT id FROM %s WHERE %s='%s' 
> AND %s='%s' 
> LIMIT 1)
> 
> but I have the question: why there is 'LIMIT 1' in update or delete? 
> doesn't it meen that it will be deleted (or updated) a random 
> row (which 
> of course fulfil where clause)? is there need to limit deletion?
> 
> --
> another idea:
> while writing code i found that adding dlr support for others 
> database 
> frontends (including libpq from postgres and - probably - unixODBC) 
> might be done little bit simple by changing some in dlr concept. the 
> idea is to write one dlr engine using dbpool, of course there 
> is need to 
> implement update and select into dbpool. problem is with 
> different sql 
> syntax in various dbs (for example 'limit 1' in mysql and 
> 'rownum < 2' 
> in oracle). the function call could be parameterized by sql 
> query (or it 
> part).
> 
> in my opinion dlr engine should look (for example) like oracle dlr 
> implementation, what do you thing about it?
> 
> 
> 
> -- 
> Marcin Sobieszczan'ski
> ________________________________
> Adv.pl Sp. z o.o - Interactive Solutions
> 
>   tel.:   + 48 71 79.74.770
>   fax.:   + 48 71 79.74.700
>   e-mail: [email protected]
>   URL:    www.adv.pl
> 
>   50-079 Wroc?aw - ul. Ruska 58/59
> _______________________________
> 
> -----BEGIN PGP PUBLIC KEY BLOCK-----
> Version: GnuPG v1.2.3 (GNU/Linux)
> 
> mQGiBD/i818RBACE5qChSekcY9HrF2a/FDiwjHvi8gENoS3VS8aYUcFwckxi33kK
> OLdsW3Vj9/ESEwlw7+Tvz7Ww9zWWB7mDC74SdU+/VcGCf4iL3Oe0l7AxHaTGvLXy
> /OxIoP665EU+VnZ9kQWwmMOr9XZZ2xN07o/T/AilZqivbQZpTCq1knl08wCg5cHF
> KMuJTy/8aXZLcOmHEId6BM8D/jSJxx4odLoFifehr4+QTXtO3Iv3Y0Hkh27Crro/
> IbWGJAdA+ACDGxt9qm9HUQNVZXQZ2+oLUK0XEv1LMfNMHEBAgj9q57sa8LCdw6MB
> tM/1/cNt9y5KCTJ0NqVF+8fSpBiaXcuCH62g9+zB9J0jYLb4pm7chlO0O/1tIGUQ
> dQrIA/4qV+VumqJ4tTxdMrboVTNhqN1kxExrBnyDCBJPtI8KfbEy6jUjSP8i1Iu+
> VDrachrSKSfc/vPGACuHQ4SbhoyXpu4aO37SxvQDX6pLkgjPBFp2m6a3+k2/0IF9
> iKC0pgMBxFbj1O72+CeNuHl9ApW6/ZXTuRIpYRdDHnfqF5GWhrQ9TWFyY2luIFNv
> Ymllc3pjemFuc2tpIChJVCBEZXBhcnRtZW50IEFkdi5wbCkgPG1hcmNpc29AYWR2
> LnBsPohhBBMRAgAhBQI/4vNfBQkB4TOABgsJCAcDAgMVAgMDFgIBAh4BAheAAAoJ
> EMPg5erRFdu1iqEAoIJ31K9c3y00dSVcp/2Wg4YUIozwAJ0W40Jeuzv/SXf1vP0l
> cF/rHep2tbkBDQQ/4vNjEAQAr1OEYkS6W2JIY5bYAV60zY89JJNz3121wHVgnMxL
> rVfmg+gJ7H6HpIvuQZl9EwIPheaRoC5ytsLtiP9IgwZ8gxDi9kdoLmVT6Nhx0lz0
> u8H8rCYpZEQS04/5L3jZOhaEf6Fn3XnnHNG/yrlvPEIyD7EaYttDENeiH48H24YX
> coMAAwUEAI2hsv0Q4uhPKnKFS+kOBTW1cWhJzAggkQzqY0fIizbu2w5WO9VG+n4H
> 8+DYn45iJF8IGTkmSSk2sMVF3Ci/1PCPOJdZVX3hZCFOr6g6n8++GjwFcbaovvfb
> SKucD+tzpll/J9ihUIVxfCL76ngEY0eps+nozZiN9ei2NfJ64PKOiEwEGBECAAwF
> Aj/i82MFCQHhM4AACgkQw+Dl6tEV27UCRgCgwpvnjfd63+FSWqIDdlj8+NS/HHYA
> nR63VNm9ToRpvPraKykRMC/t6ogi
> =Ooyf
> -----END PGP PUBLIC KEY BLOCK-----
> 
> this key is also avilable at pgp.mit.edu with id 0xD115DBB5:
> http://pgp.mit.edu:11371/pks/lookup?search=0xD115DBB5&op=index
> 
>