Re: External search engine, advice

Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > freedb=# select * from cdsongs where songid = ftss_results() ;
> > ERROR:  Set-valued function called in context that cannot accept a set
> 
> '=' is a scalar operation.  Try
> 
> select * from cdsongs where songid IN (select ftss_results());

I was afraid you'd say that. That does not use indexes.

It is pointless to use a text search engine if the result has to perform a
table scan anyway.

If I do:

create temp table fubar as select ftss_results() as songid;
select * from cdsongs where songid = fubar.songid;

That works, but that is slow and a lot of people have emotional difficulties
with using temporary tables. (Oracle syndrome) Also, an 'IN' clause does not
preserve the order of the results, where as a join should.