MySQL insert() and instr() equiv

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