Re: Strange select query

I wonder if that wouldn't be better written as:

select * from TA as a, TB as b, TC as c where
a.index=b.referencefield and b.other_referencefield=c.index and (
     (position('some text' in a.textfield)>0) or
     (position('some text' in b.textfield)>0) or
     (position('some text' in c.textfield)>0)
);

so that you only check your link fields once.  Further, if you just want to see
whether a.textfield contains 'some text', you don't have to do it Visual Basic
style.  Postgresql supplies the ~ and LIKE operators for just that situation:

select * from TA as a, TB as b, TC as c where
a.index=b.referencefield and b.other_referencefield=c.index and (
     a.textfield LIKE '%some text%' or
     b.textfield LIKE '%some text%' or
     c.textfield LIKE '%some text%'
)

Check the docs for LIKE, ILIKE, ~~ and ~ operators for details.
http://www.postgresql.org/idocs/index.php?functions-matching.html.

HTH.

Wes Sheldahl




"Partyka Robert" <bobson%saturn.alpha.pl@interlock.lexmark.com> on 09/12/2001
05:16:05 AM

To:   Einar Karttunen <ekarttun%cs.helsinki.fi@interlock.lexmark.com>
cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
      Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] Strange select query


Hello

> The query is valid you should get more ram or rethink your query :-)
thx for explain how meny records it will return,
I know that was my mistake because the correct query I need was
something like:

select * from TA as a, TB as b, TC as c where
(position('some text' in a.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index) or
(position('some text' in b.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index)
or
(position('some text' in c.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index);

I was just wonder if that was bad query and take never ending loop
in backend or just return so many records.
In fact i should get this reflection self ... so maybe it was brain lock
that I dont saw this explanation (sig 'take some rest' from brain? ;-)
). I was suggested by result show by psql (I saw one the same
record showed about 100 times before I ^C the query so i think 'oh.
its probably never ending loop.').

regards
Robert Partyka

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly