Re: External search engine, advice

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)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.