Re: [SQL] simple web search
I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] simple web search
On Fri, Feb 23, 2007 at 10:01:22AM -0800, chester c young wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. Thanks, this looks promising. The union and view ideas are indeed inspiring. What is that ::name cast for? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple web search
create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... What is that ::name cast for? it's not needed here - sorry. name is the data type pg uses for table names tc. it's frequently a good idea to cast to name when when messing around in the data dictionary. TV dinner still cooling? Check out Tonight's Picks on Yahoo! TV. http://tv.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple web search
Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? You may want to take a look at contrib/tsearch2. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple web search
I think contrib/tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is what you need. Oleg On Fri, 23 Feb 2007, chester c young wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] simple web search
On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] simple web search
On Fri, 23 Feb 2007, Louis-David Mitterrand wrote: On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :) Don't be afraid, it's not so difficult to start. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
