Thread: MySQL insert() and instr() equiv
When using MySQL I have one select that uses MySQL functions so I am wondering about the best way to go to end up with the same result in PostgreSQL. uid is an email address stored in the passwd table as user@domain.com and this construct allows an incoming username such as "user.domain.com" to be compared to the stored "user@domain.com". SELECT wpath FROM passwd WHERE uid="\L" OR insert(uid,instr(uid,'@'),1,'.')="\L" --markc
Mark Constable <markc@renta.net> writes:
> uid is an email address stored in the passwd table as user@domain.com
> and this construct allows an incoming username such as "user.domain.com"
> to be compared to the stored "user@domain.com".
> SELECT wpath FROM passwd WHERE uid="\L" OR insert(uid,instr(uid,'@'),1,'.')="\L"
Well, if you want to write it exactly that way you'd need to write
insert() and instr() functions, which would probably take about five
minutes in any reasonably decent string-mashing language such as plperl.
You could do it in plpgsql but it'd be more painful. (There's an
example of coding instr() in the back of the plpgsql manual chapter,
but it's intended to match Oracle's version of instr() which might not
be quite like MySQL's.)
But, if you're not wedded to that particular way, why not use replace()?
SELECT wpath FROM passwd WHERE uid="\L" OR replace(uid, '@', '.')="\L"
regards, tom lane
On Sun, Jun 18, 2006 at 02:06:28AM +1000, Mark Constable wrote: > When using MySQL I have one select that uses MySQL functions so I am > wondering about the best way to go to end up with the same result in > PostgreSQL. You might be interested in the MySQL Compatibility Functions: http://pgfoundry.org/projects/mysqlcompat/ > uid is an email address stored in the passwd table as user@domain.com > and this construct allows an incoming username such as "user.domain.com" > to be compared to the stored "user@domain.com". > > SELECT wpath FROM passwd WHERE uid="\L" OR insert(uid,instr(uid,'@'),1,'.')="\L" The compatibility functions mentioned above can do this. See also "String Functions and Operators" in the documentation: http://www.postgresql.org/docs/8.1/interactive/functions-string.html Example: overlay(uid PLACING '.' FROM position('@' IN uid) FOR 1) In 8.1 you could use regexp_replace: regexp_replace(uid, '@', '.') For more complex searching and/or replacing you could write a function in PL/Perl, PL/Python, etc. -- Michael Fuhr
On Sunday 18 June 2006 02:33, Tom Lane wrote: > > uid is an email address stored in the passwd table as user@domain.com > > and this construct allows an incoming username such as "user.domain.com" > > to be compared to the stored "user@domain.com". > But, if you're not wedded to that particular way, why not use replace()? I only decided to ditch MySQL tonight so I'm within the first 1/2 dozen hours of using pgsql for almost the first time. I've lost a fair amount of hair with the basics of db and user setup and close to burnout. > SELECT wpath FROM passwd WHERE uid="\L" OR replace(uid, '@', '.')="\L" Excellent. Just the double quotes needed to be changed to single quotes to avoid this error and replace() indeed works for my needs. ERROR: column "user.domain.com" does not exist Another anti-burnout question, how would I turn on the ability to view any SQL requests in the logfile ? No doubt there is an answer in the list archives somewhere but my first few searches brought up nothing useful. --markc
On Sun, Jun 18, 2006 at 04:18:23 +1000, Mark Constable <markc@renta.net> wrote: > > Another anti-burnout question, how would I turn on the ability > to view any SQL requests in the logfile ? I think this section of the manual will answer your question: http://developer.postgresql.org/docs/postgres/runtime-config-logging.html (Though note I pointed you to the developer version, it might be different from what applies to the version of Postgre you are running.) > No doubt there is an answer in the list archives somewhere but > my first few searches brought up nothing useful. The manual that comes with Postgres is very good. Normally I would suggest looking at it first, before trying to search the archives.
Not sure this is the right answer: in older version you could enable it via the postgresql.conf file, modifing the variable log_statement and setting that to true. Also, you should check the syslog level variable in the same file. Regards Marco On 6/17/06, Mark Constable <markc@renta.net> wrote: > On Sunday 18 June 2006 02:33, Tom Lane wrote: > > > uid is an email address stored in the passwd table as user@domain.com > > > and this construct allows an incoming username such as "user.domain.com" > > > to be compared to the stored "user@domain.com". > > > But, if you're not wedded to that particular way, why not use replace()? > > I only decided to ditch MySQL tonight so I'm within the first > 1/2 dozen hours of using pgsql for almost the first time. I've > lost a fair amount of hair with the basics of db and user setup > and close to burnout. > > > SELECT wpath FROM passwd WHERE uid="\L" OR replace(uid, '@', '.')="\L" > > Excellent. Just the double quotes needed to be changed to single > quotes to avoid this error and replace() indeed works for my needs. > > ERROR: column "user.domain.com" does not exist > > Another anti-burnout question, how would I turn on the ability > to view any SQL requests in the logfile ? > > No doubt there is an answer in the list archives somewhere but > my first few searches brought up nothing useful. > > --markc > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Marco Bizzarri http://notenotturne.blogspot.com/
On Jun 17, 2006, at 1:18 PM, Mark Constable wrote: > On Sunday 18 June 2006 02:33, Tom Lane wrote: >>> uid is an email address stored in the passwd table as >>> user@domain.com >>> and this construct allows an incoming username such as >>> "user.domain.com" >>> to be compared to the stored "user@domain.com". > >> But, if you're not wedded to that particular way, why not use >> replace()? > > I only decided to ditch MySQL tonight so I'm within the first > 1/2 dozen hours of using pgsql for almost the first time. I've > lost a fair amount of hair with the basics of db and user setup > and close to burnout. You should hop on the IRC channel (#postgresql on irc.freenode.net). There's almost always people on, and it's a great way to get help. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461