Обсуждение: problem with select
Hi all,
I have an problem and I can't find a solution.
I have a products database like this:
code varchar,
category varchar,
title varchar,
manufacturer varchar,
description varchar,
desc_de varchar,
comment varchar,
price float,
...
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 !
This is in conjunction with minivend, an internet shopping mall,
the query is generated as follows:
....<TD [var TABLEFG] ALIGN=RIGHT> <FONT SIZE=-1 FACE=ARIAL,HELVETICA style="text-decoration: none">
<INPUTTYPE="hidden" NAME="mv_searchtype" VALUE="sql"> <INPUT TYPE="hidden" NAME=mv_matchlimit VALUE="3">
<INPUTTYPE="hidden" NAME=mv_sql_query VALUE="select code,category from products where comment ~* ?">
^^^ <INPUT MAXLENGTH="30" NAME="mv_searchspec"
SIZE="13"VALUE=""><BR> <FONT FACE="arial,helvetica" SIZE="-1"> <INPUT TYPE="submit"
VALUE="[loc]Search[/loc]"><BR>
....
The question mark get's replaced by the contents of the HTML input field,
and I have no chance to create a temporary table here (security issue).
I must create a view or a rule or something to get a query like this:
VALUE="select foobar from myview where comment ~* ?">
How can this be done ?
Thanks in Advance,
Holm
--
FreibergNet Systemhaus GbR Holm Tiffe * Administration, Development
Systemhaus für Daten- und Netzwerktechnik phone +49 3731 781279
Unternehmensgruppe Liebscher & Partner fax +49 3731 781377
D-09599 Freiberg * Am St. Niclas Schacht 13 http://www.freibergnet.de/
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
Holm, Can you clarify the problem a bit? > So far so good, but I have the problem that I have the value 'abc' > only one times ! Do you mean you only get one row as a result? You might do better with using "group by" instead of distinct, if you only want a distinct code. I.e. select code,category from products where code ~* 'abc' or category ~* 'abc' or manufacturer ~* 'abc' or description ~* 'abc' or comment ~* 'abc' group by code; Troy > > Hi all, > > I have an problem and I can't find a solution. > > I have a products database like this: > > code varchar, > category varchar, > title varchar, > manufacturer varchar, > description varchar, > desc_de varchar, > comment varchar, > price float, > ... > > 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 ! > > This is in conjunction with minivend, an internet shopping mall, > the query is generated as follows: > > .... > <TD [var TABLEFG] ALIGN=RIGHT> > <FONT SIZE=-1 FACE=ARIAL,HELVETICA style="text-decoration: none"> > <INPUT TYPE="hidden" NAME="mv_searchtype" VALUE="sql"> > <INPUT TYPE="hidden" NAME=mv_matchlimit VALUE="3"> > <INPUT TYPE="hidden" NAME=mv_sql_query > VALUE="select code,category from products where comment ~* ?"> > ^^^ > <INPUT MAXLENGTH="30" NAME="mv_searchspec" SIZE="13" VALUE=""><BR> > <FONT FACE="arial,helvetica" SIZE="-1"> > <INPUT TYPE="submit" VALUE="[loc]Search[/loc]"><BR> > .... > > The question mark get's replaced by the contents of the HTML input field, > and I have no chance to create a temporary table here (security issue). > > I must create a view or a rule or something to get a query like this: > > VALUE="select foobar from myview where comment ~* ?"> > > How can this be done ? > > > Thanks in Advance, > Holm > -- > FreibergNet Systemhaus GbR Holm Tiffe * Administration, Development > Systemhaus für Daten- und Netzwerktechnik phone +49 3731 781279 > Unternehmensgruppe Liebscher & Partner fax +49 3731 781377 > D-09599 Freiberg * Am St. Niclas Schacht 13 http://www.freibergnet.de/ > > > ************ > >
Tom Lane wrote: > 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 Hm, clever. It is almost working, my database isn't that big that memory becomes a problem; the machine has 512MB. But why is nothing found if one value of a column contains NULL ? select distinct code,category from products where (code || '|' || category|| '|' || comment || '|' || description || '|'|| desc_de) ~* 'kde'; code|category ------+-------- 06-001|KDE (1 row) shop=> update products set comment = Null where code ='06-001'; UPDATE 1 shop=> select (comment || '|' || code) from products where code ~* '06-001'; ?column? -------- (1 row) Holm -- FreibergNet Systemhaus GbR Holm Tiffe * Administration, Development Systemhaus für Daten- und Netzwerktechnik phone +49 3731 781279 Unternehmensgruppe Liebscher & Partner fax +49 3731 781377 D-09599 Freiberg * Am St. Niclas Schacht 13 http://www.freibergnet.de/
Holm Tiffe <holm@freibergnet.de> writes:
> It is almost working, my database isn't that big that memory becomes a
> problem; the machine has 512MB.
> But why is nothing found if one value of a column contains NULL ?
Because nulls propagate: any operator whose input is NULL produces NULL.
The only things that don't return NULL are the special syntaxes IS NULL,
IS NOT NULL, and CASE expressions using them (in particular the CASE
shortcuts COALESCE, etc).
You probably want to write COALESCE(field, '') in place of just field
for any column that can be NULL.
regards, tom lane
Holm Tiffe ha scritto: > Tom Lane wrote: > > > 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 > > Hm, clever. > > It is almost working, my database isn't that big that memory becomes a > problem; the machine has 512MB. > > But why is nothing found if one value of a column contains NULL ? > > select distinct code,category from products where (code || '|' || category > || '|' || comment || '|' || description || '|' || desc_de) ~* 'kde'; > code|category > ------+-------- > 06-001|KDE > (1 row) > > shop=> update products set comment = Null where code ='06-001'; > UPDATE 1 > shop=> select (comment || '|' || code) from products where code ~* '06-001'; > ?column? > -------- > > Try using the COALESCE function: select distinct code,category from products where (code || '|' || coalesce(category,' ') || '|' || coalesce(comment,' ') || '|' || coalesce(description,' ') || '|' || coalesce(desc_de,' ')) ~* 'kde'; José