Re: Hope it is not too offtopic

On Wed, Mar 21, 2001 at 08:03:47PM +0100, Jan Ploski wrote:
> > I have a database of ISO standards ... my clients want to be able to do a
> > full document search ... but it's not a document ... it's a database
> > Is there a way (more likely a query question) to
> > select * from tablename where <anyfield> like '%term%'??
>
> This query will work, but I suspect this is a very inefficient way
> of doing a full text search.

not literally, it wouldn't:

select * from tbl where <oops> like '%pat%';
--ERROR: parser: parse error at or near "<"

i think the question was intended to ask if it's possible to
specify that postgres search EVERY field for a pattern, INSTEAD of
a PARTICULAR field for a pattern:

-- i.e. rather than the tedious (and workable):
select * from tbl
where fld01 like '%term%'
or fld02 like '%term%'
or fld03 like '%term%'
or fld04 like '%term%'
or fld05 like '%term%'
or fld06 like '%term%'
or fld07 like '%term%'
or fld08 like '%term%'
or fld09 like '%term%'
or fld10 like '%term%'
or fld11 like '%term%'
;
-- hopes were high for something like this instead:
select * from tbl
where * like '%term%'
;
-- or maybe this:
select * from tbl
where (fld02 | fld15 | fld42) like '%term%'
;

which <guess confidence=purty-darn-high> i'd bet isn't even on
the 'to-do' list. </guess>

<guess confidence=not-very-low> probably, what is needed,
instead, is a re-structuring of the database. </guess>

--

case in point: i had some lookup tables such as

create table l_menu (
id varchar(5),
other varchar(50),
en varchar(50), -- english description of this menu
es varchar(50), -- espanol description of this menu
fr varchar(50), -- francais description of this menu
de varchar(50), -- deutsch description of this menu
primary key(id)
);

so that in perl, i could

$sth = $dbh->prepare(<<SQL);
select other,$LANG
from l_menu
...yadayada...
SQL

but i eventually ran into logistical snags, so now i've got

create table l_menu (
id varchar(5),
other varchar(50),
primary key(id)
);
create table d_menu (
id varchar(5) references l_menu(id),
lang varchar(5),
descr varchar(50),
primary key(id)
);

which is still reasonably easy to work with, in perl:

$sth = $dbh->prepare(<<SQL);
select other,descr
from l_menu,d_menu
where l_menu.id=d_menu.id and d_menu.lang='$LANG'
...yadayada...
SQL

except now it's easier to munge on the database end -- for
example, to add new languages.

--

relating it to this thread (i had to get around to this
eventually :) imagine if i was looking for a description in any
language that contained 'xyz':

select id from l_menu
where en like '%xyz%'
or es like '%xyz%'
or fr like '%xyz%'
or de like '%xyz%';
-- ick! (old snanky style)

contrast with

select distinct l_menu.id from l_menu,d_menu
-- or, "select l_menu.id,lang from l_menu,d_menu"
where descr contains '%xyz%'
and l_menu.id = d_menu.id;
-- yummy! (new chrome-plated dual-exhaust high-octane style)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!