Richard Huxton wrote:
Madison Kelly wrote:
nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('mkelly@xxxxxxxx');
local
-------
(0 rows)
Not work?
I don't think IN does what you think it does. It's not a substring-test,
but a set test:
SELECT 1 WHERE 'x' IN ('a','b','c','x');
SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble);
You could mess around with substring() and length() or I'd use LIKE.
If it's just a domain you're looking for though, might be most efficient
to strip the leading part off your value with regexp_replace().
Yeah, that was my problem. I thought I was using the section following
the '@'. =/
I've been using Postgres for a while now, but only recently getting into
some of the fancier stuff. Until now, I've usually written the program
using PgSQL so I could manipulate the data as I needed. Now I am using
PgSQL as a backend for a few other applications so I am restricted to
using PgSQL to manipulate the data.
It's all left me feeling quite n00bish again. ;)
I did figure out a query that worked:
SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
AND u.usr_email||'@'||d.dom_name IN ('mkelly@xxxxxxxx');
Though this may not be the most efficient. In my case, the 'usr_email'
is the LHS of the '@' sign and 'dom_name' is the domain name. If I
wanted to use (I)LIKE, how would I have matched just the domain section
of 'mkelly@xxxxxxxx' in 'dom_name'?
I'll go read up, now that I've got some key words to search the docs on.
Thanks kindly!
Madi
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
