Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id CD2939FC26F for ; Fri, 23 Feb 2007 14:08:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 55402-09 for ; Fri, 23 Feb 2007 14:08:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from zenon.apartia.fr (zenon.apartia.fr [82.66.93.83]) by postgresql.org (Postfix) with ESMTP id 4A8B49FC265 for ; Fri, 23 Feb 2007 14:08:02 -0400 (AST) Received: from trajan.apartia.fr (trajan.apartia.fr [10.0.3.121]) by zenon.apartia.fr (Postfix) with ESMTP id 5CA66F0ABE708 for ; Fri, 23 Feb 2007 19:08:01 +0100 (CET) Received: by trajan.apartia.fr (Postfix, from userid 1000) id 332A42534C2A; Fri, 23 Feb 2007 19:07:57 +0100 (CET) Date: Fri, 23 Feb 2007 19:07:57 +0100 From: Louis-David Mitterrand To: sql pgsql Subject: Re: simple web search Message-ID: <20070223180757.GA22649@apartia.fr> Mail-Followup-To: sql pgsql References: <20070223162731.GA25577@apartia.fr> <784381.72635.qm@web54306.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <784381.72635.qm@web54306.mail.yahoo.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.347 tagged_above=0 required=5 tests=AWL, BAYES_50, RCVD_IN_NJABL_DUL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: * X-Archive-Number: 200702/231 X-Sequence-Number: 27565 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?