pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple web search
8+ messages / 4 participants
[nested] [flat]
* simple web search
@ 2007-02-23 16:27 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 2 replies; 8+ messages in thread
From: Louis-David Mitterrand @ 2007-02-23 16:27 UTC (permalink / raw)
To: pgsql-sql
Hello,
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?
Thanks,
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:01 chester c young <chestercyoung@yahoo.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 2 replies; 8+ messages in thread
From: chester c young @ 2007-02-23 18:01 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
> 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:07 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: chester c young <chestercyoung@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Louis-David Mitterrand @ 2007-02-23 18:07 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:20 chester c young <chestercyoung@yahoo.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 8+ messages in thread
From: chester c young @ 2007-02-23 18:20 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
> > 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:30 Oleg Bartunov <oleg@sai.msu.su>
parent: chester c young <chestercyoung@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Oleg Bartunov @ 2007-02-23 18:30 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:31 Joe <dev@freedomcircle.net>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 8+ messages in thread
From: Joe @ 2007-02-23 18:31 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:34 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 8+ messages in thread
From: Louis-David Mitterrand @ 2007-02-23 18:34 UTC (permalink / raw)
To: pgsql-sql
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 :)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: simple web search
@ 2007-02-23 18:50 Oleg Bartunov <oleg@sai.msu.su>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 8+ messages in thread
From: Oleg Bartunov @ 2007-02-23 18:50 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-02-23 18:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-23 16:27 simple web search Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-23 18:01 ` chester c young <chestercyoung@yahoo.com>
2007-02-23 18:07 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-23 18:20 ` chester c young <chestercyoung@yahoo.com>
2007-02-23 18:30 ` Oleg Bartunov <oleg@sai.msu.su>
2007-02-23 18:31 ` Joe <dev@freedomcircle.net>
2007-02-23 18:34 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-23 18:50 ` Oleg Bartunov <oleg@sai.msu.su>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox