Search (select) options

Jeff Davis wrote:

I would like to be able to use searches that seem somewhat intelligent.
Can you 'ORDER BY' number of matching 'OR' clauses? For example, someone
searches for "x y z", so I would do "select * from mytable where col1
like '%x%' or col1 like '%y%' or col1 like '%z%';", but I want it to
order by number of matches (so a match of y and z would turn up before a
match of just x).

If anyone has suggestions, or can point me to some reading, I would
really appreciate it. The only thing I can think of is a complicated
application-side program.

chrisb=# create table t(a text, b text, c text);
CREATE
chrisb=# insert into t values(null, 'x', null);
INSERT 18955 1
chrisb=# insert into t values(null, 'x', 'x');
INSERT 18956 1
chrisb=# insert into t values(null, 'x', null);
INSERT 18957 1
chrisb=# insert into t values(null, 'x', 'x');
INSERT 18958 1
chrisb=# insert into t values('x', 'x', 'x');
INSERT 18959 1
chrisb=# insert into t values(null, null, null);
INSERT 18960 1
chrisb=# select * from t;
a | b | c
---+---+---
| x |
| x | x
| x |
| x | x
x | x | x
| |
(6 rows)

chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
order by match;
a | b | c | match
---+---+---+-------
| | | 0
| x | | 1
| x | | 1
| x | x | 2
| x | x | 2
x | x | x | 3
(6 rows)

chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
order by match desc;
a | b | c | match
---+---+---+-------
x | x | x | 3
| x | x | 2
| x | x | 2
| x | | 1
| x | | 1
| | | 0
(6 rows)

Perhaps you can use the UNION statement like so...

SELECT * FROM mytable WHERE col1='x'
UNION
SELECT * FROM mytable WHERE col1='y'
UNION
SELECT * FROM mytable WHERE col1='z'

Although, I have never tried stacking them before so I don't know if the
above will work (with more than one UNION).

Of course, this means there are 3 separate select statements instead of
your 1 but perhaps it isn't as inefficient as it sounds since pg should
process the whole transaction in the one commit.

Jeff Davis wrote:

Hello,

Wednesday, August 16, 2000, 11:44:44 AM, you wrote:

Hd> Perhaps you can use the UNION statement like so... [...]
Hd> Although, I have never tried stacking them before so I don't know if the
Hd> above will work (with more than one UNION).

UNION works (fortunately) with 100 SELECTs at least!

Although when I forgot to close the DB connection (PHP) I got an ugly
"out of file descriptors" error on the server (and a DOS for about 5
mins until I could ssh in) as each table (with indexes) required 8
file descriptors and my box only had 4096 free.

Does anyone know how long a PostGRES SQL statement can be? In the near
future, my above-mentioned query will grow to a few hundred SELECTS
(Phorum) and I'm wondering how it will cope (but too lazy to
experiment) ???

Best regards,

Barry mailto:barry@penrallt.clara.co.uk

Barry Hill <barry@penrallt.clara.co.uk> writes:

Does anyone know how long a PostGRES SQL statement can be? In the near
future, my above-mentioned query will grow to a few hundred SELECTS
(Phorum) and I'm wondering how it will cope (but too lazy to
experiment) ???

As of 7.0 there's no hard upper limit. UNIONing a few hundred SELECTs
might take longer than you really want to wait however ... perhaps some
rethinking of your data model is in order.

regards, tom lane