2

When concatenating tsvectors from setweight in an SQL query, it throws a syntax error:

ERROR: syntax error at or near "||"

It works fine if I try it with a single tsvector returned by setweight, and if I try to wrap the entire thing in another to_tsvector call, it errors with the reason that there is no to_tsvector(tsvector) function, so the concatenation is indeed forming a tsvector. 

SELECT *, ts_rank_cd(textsearch, query) AS score
FROM products, plainto_tsquery('awesome shirt') query, 
   setweight(to_tsvector(coalesce(title, '')), 'A') ||
   setweight(to_tsvector(coalesce(description, '')), 'B') ||
   setweight(to_tsvector(coalesce(tags, '')), 'C') ||
   setweight(to_tsvector(coalesce(vendor, '')), 'D') textsearch
WHERE shop_url='somedomain.com' AND query @@ textsearch
ORDER BY score DESC
LIMIT 20 OFFSET 0;

I've tried wrapping it in a subquery, but that makes it a record, which causes issues with ts_rank_cd, since it's expecting textsearch to be of type tsvector. How can I get this concatented tsvector to work in this query?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Brandon Anzaldi
  • 6,884
  • 3
  • 36
  • 55

1 Answers1

2

Assuming the columns title, description etc are supposed to refer back to the table products ...

The syntax error you see is unrelated to text search per se. This should work:

SELECT *, ts_rank_cd(textsearch, query) AS score
FROM   products
CROSS  JOIN LATERAL plainto_tsquery('awesome shirt') query
CROSS  JOIN LATERAL (
   SELECT setweight(to_tsvector(coalesce(title      , '')), 'A')
       || setweight(to_tsvector(coalesce(description, '')), 'B')
       || setweight(to_tsvector(coalesce(tags       , '')), 'C')
       || setweight(to_tsvector(coalesce(vendor     , '')), 'D')
   ) ts (textsearch)
WHERE  ...

Why?

Because this:

SELECT ...
FROM   products, plainto_tsquery('awesome shirt') query ...

is an implicit CROSS JOIN LATERAL in the FROM clause. Omitting the LATERAL keyword is allowed for functions. And the comma is mostly (see link at the end) equivalent to a CROSS JOIN.

Would work for this, too:

SELECT ...
FROM   products
     , plainto_tsquery('awesome shirt') query
     , setweight(to_tsvector(coalesce(title, '')), 'A')  -- just another function
...

Hence:

It works fine if I try it with a single tsvector returned by setweight

But the same short syntax is not allowed for other expressions like setweight(...) || setweight(...). Those need to be wrapped in a SELECT statement which requires an explicit LATERAL keyword to allow the reference to a "lateral" table in the FROM list. Like demonstrated above. Or, shorter:

SELECT *, ts_rank_cd(textsearch, query) AS score
FROM   products
     , plainto_tsquery('awesome shirt') query
     , LATERAL (
   SELECT setweight(to_tsvector(coalesce(title      , '')), 'A')
       || setweight(to_tsvector(coalesce(description, '')), 'B')
       || setweight(to_tsvector(coalesce(tags       , '')), 'C')
       || setweight(to_tsvector(coalesce(vendor     , '')), 'D')
   ) ts (textsearch)
WHERE  ...

Related answer with more explanation for both CROSS JOIN and LATERAL:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228