pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with select
7+ messages / 5 participants
[nested] [flat]
* problem with select
@ 1999-08-30 12:04 Holm Tiffe <holm@freibergnet.de>
0 siblings, 2 replies; 7+ messages in thread
From: Holm Tiffe @ 1999-08-30 12:04 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] problem with select
@ 1999-08-30 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Holm Tiffe <holm@freibergnet.de>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 1999-08-30 14:43 UTC (permalink / raw)
To: holm@freibergnet.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] problem with select
@ 1999-08-30 16:47 tjk@tksoft.com <tjk@tksoft.com>
parent: Holm Tiffe <holm@freibergnet.de>
1 sibling, 0 replies; 7+ messages in thread
From: tjk@tksoft.com @ 1999-08-30 16:47 UTC (permalink / raw)
To: holm@freibergnet.de; +Cc: pgsql-sql
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/
>
>
> ************
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] problem with select
@ 1999-08-31 06:29 Holm Tiffe <holm@freibergnet.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Holm Tiffe @ 1999-08-31 06:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: holm@freibergnet.de; pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] problem with select
@ 1999-08-31 12:23 José Soares <jose@sferacarta.com>
parent: Holm Tiffe <holm@freibergnet.de>
1 sibling, 0 replies; 7+ messages in thread
From: José Soares @ 1999-08-31 12:23 UTC (permalink / raw)
To: holm@freibergnet.de; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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é
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] problem with select
@ 1999-08-31 13:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Holm Tiffe <holm@freibergnet.de>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 1999-08-31 13:31 UTC (permalink / raw)
To: holm@freibergnet.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [SQL] problem with select
@ 1999-08-31 16:23 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jackson, DeJuan @ 1999-08-31 16:23 UTC (permalink / raw)
To: holm@freibergnet.de, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Why? Because:
NULL <any operator> <any value including NULL> = NULL
Therefore:
(comment || code) = NULL, because comment is NULL
You must remember that NULL is not the same as an empty string;
NULL = undefined.
Hope this helps,
DEJ
> -----Original Message-----
> From: Holm Tiffe [SMTP:holm@freibergnet.de]
> Sent: Tuesday, August 31, 1999 1:29 AM
> To: Tom Lane
> Cc: holm@freibergnet.de; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] problem with select
>
> 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/
>
>
> ************
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-08-31 16:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-30 12:04 problem with select Holm Tiffe <holm@freibergnet.de>
1999-08-30 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-31 06:29 ` Holm Tiffe <holm@freibergnet.de>
1999-08-31 12:23 ` José Soares <jose@sferacarta.com>
1999-08-31 13:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-30 16:47 ` tjk@tksoft.com <tjk@tksoft.com>
1999-08-31 16:23 RE: [SQL] problem with select Jackson, DeJuan <djackson@cpsgroup.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox