Re: [SQL] problem with select

Holm Tiffe <holm@freibergnet.de> writes:
> What I try to find is a solution for a search in this table like this:

> select distinct code,category from products where code ~* 'abc' or
> category ~* 'abc' or manufacturer ~* 'abc' or description ~* 'abc'
> or comment ~* 'abc' order by code;

> So far so good, but I have the problem that I have the value 'abc'
> only one times !

How about

select distinct code,category from products where
(code || category || manufacturer || ...) ~* 'abc';

Actually you'd probably want to also concatenate some separator markers,
maybe (code || '|' || category || ...), so that you didn't get bogus
matches across fields, like where code ends in 'a' and category starts
with 'bc'.

Note that this select will be a bit of a memory hog because
text-slinging is very wasteful of space in 6.5 (the intermediate results
from the concatenate operators don't get freed till end of transaction).
So it might not be workable if you have a large database.  I hope to see
that fixed for 6.6 or 6.7.
        regards, tom lane