CODEWITHSUNDEEP
postgresqlfull-text-searchpostgresql-9.4
Mad Wombat
Mad Wombat

Reputation: 15105

Supplying tsvector for full search in postgres as part of FROM

I have a text search query that looks like this

with query as (select to_tsquery('sometext'))
select
    t1.id
from 
    table1 as t1,
    to_tsvector(t1.f1) as vector
where
    (select * from query) @@ vector

This works as expected. But when I try to add more fields to the vector like this

with query as (select to_tsquery('sometext'))
select
    t1.id
from 
    table1 as t1,
    to_tsvector(t1.f1) || to_tsvector(t1.f2) as vector
where
    (select * from query) @@ vector

I get a syntax error near ||. What am I doing wrong here?

My database is PostgreSQL 9.4.

Upvotes: 0

Views: 35

Answers (1)

jjanes
jjanes

Reputation: 44137

The FROM list is allowed to have a function call as if it were a table, but not an operator expression. You can translate it to the equivalent function call:

with query as (select to_tsquery('sometext'))
select
    t1.id
from 
    table1 as t1,
    tsvector_concat(to_tsvector(t1.f1), to_tsvector(t1.f2)) as vector
where
    (select * from query) @@ vector

Upvotes: 1

Related Questions