Concatenating two Text fields from the same tuple
От | psql-mail@freeuk.com |
---|---|
Тема | Concatenating two Text fields from the same tuple |
Дата | |
Msg-id | E19Y7hD-0007gl-00@buckaroo.freeuk.net обсуждение исходный текст |
Ответы |
Re: Concatenating two Text fields from the same tuple
|
Список | pgsql-general |
Apologies if this is a repost - I tried sending it yesterday and haven' t seen it in the forum yet. I am currently writing a perl script to convert the string a user supplies to a search engine into SQL. The user supplies a string in the same foramt as google uses - e.g. "cat -dog" finds records containing cat but not dog. The code i have written works well but i have just realised that if ( unknown to the user) the DB needs to search across more than one attribute, a number of problems arise. For the above query the code currently outputs WHERE head ILIKE '%cat%' AND head NOT ILIKE '%dog%' OR body ILIKE '%cat%' AND body NOT ILIKE '%dog%'; which is equivalent to: WHERE ( attr1 ILIKE '%cat%' AND attr1 NOT ILIKE '%dog%') OR ( attr2 ILIKE '%cat%' AND attr2 NOT ILIKE '%dog%'); This gives undesired behaviour - if 'cat' is found in the attr1 , and ' dog' is not found in attr1 of then the tuble is returned, even if dog is found in attr2 of the tuple. What is really wanted is: WHERE ( attr1 ILIKE '%cat%' OR attr2 ILIKE '% cat%' ) AND attr1 NOT ILIKE '%dog%' AND attr2 NOT ILIKE '%dog%; Question1 1: Is there already a google style search string parser out there somewhere? Question 2: If not - is it possible for me to concatenate attr1 and attr2 before i do the search as this would remove the logic problems. ( and if it is possible - is it efficient?) Question 3: If its not possible to concatenate them - any other sugestions?! Thanks!
В списке pgsql-general по дате отправления: