How to translate tag area into a PostgreSQL query?

Question

I have a tag area where 0...N tags can be chosen. The tag area gives a comma separated list which will then be sent to the server for further processing. For example, the tag csv-list can look like this:

var taglist = ['important', 'less important']

I create an array out of it for it to be processed by LIKE ANY.

 var taglistArray = pgp.as.array(taglist);

There is a possible way with LIKE ANY but I could not get it to work.

 select * from $1:name WHERE importance LIKE ANY $4:list
 ...
 ['tablename', field2, field3, taglistArray]

error: syntax error at or near "'array[''important'',''less important'']'"

There seem to be several issues if the tag area is empty (hence the query should return everything and not nothing) and the ability to query a list instead of just one keyword.

How can a list of tags be translated into a SQL query in the best way using pg-promise?

Answer 1

As per documentation examples, you use arrays directly, and not pre-convert them into strings:

var taglist = ['one', 'two'];
db.any('select * from $1:name WHERE importance LIKE ANY($2)', ['important', tagList])

And your code formats the array twice, and hence the double-escaping issue. It is only for IN($2:list) you would use the :list filter, but for ANY you need the array directly, i.e. ANY($2), without any filter.

Also, the correct syntax for ANY is with parentheses - ANY($2).