I want to build a table where each row contains a string and the number of rows where that string appears as a prefix

Basically I want

select count(*) from "myTable" where tsfield @@ (p||':*')::tsquery

for each value of p in an array.

How can I write a query to do this?

CodePudding user response：

Unnest the array and join:

SELECT arr.p, count(*)
FROM "myTable"
   JOIN unnest('{...}') AS arr(p)
      ON tsfield @@ (arr.p||':*')::tsquery
GROUP BY arr.p;