Re: Hope it is not too offtopic - Mailing list pgsql-general

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@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!