How can I write a database function with where in query-CodePudding

I am changing a db function and I need to add a query for items matching their category id with any items from an array i give.

Example input:

let categories = ['10272111', '165796011', '3760911', '9223372036854776000','3760901','7141123011']

Part of db function:

(case when brand is null then '' else 'AND ra.brand ILIKE ''%%'||brand||'%%''' end),
(case when category is null then '' else 'AND ra.root_category IN ('||category||')' end),
(case when asin is null then '' else 'AND ra.asin ILIKE ''%%'||asin||'%%''' end),

But I keep getting malformed array literal: "AND ra.root_category IN (" error and I can't find any resource for the right syntax.

Edit: This is the definition at the top of the function: category text[] DEFAULT NULL::text[],

CodePudding user response：

As a generic approach, use = any and a string of comma-separated values instead of IN.
First change the function declaration, change category text[] to category text.
Then change

(case when category is null then '' else 'AND ra.root_category IN ('||category||')' end)

(case when category is null then '' else 'AND ra.root_category = any(''{'||category||'}'')' end)

Finally in JS convert the categories array to a string, i.e. .join(',') the category array and pass the resulting string to the function.

Unrelated but as far as I see dynamic SQL is not needed. Try

AND (category is null OR ra.root_category = any('{'||category||'}'))
-- or the equivalent 
AND (category is null OR ra.root_category = any(string_to_array(category, ',')))

CodePudding user response：

I did it!

Function decleration: category text DEFAULT NULL::text

(case when category is null then '' else 'AND ra.root_category IN '||category end),

is the query and I am sending the array this way:

let categories = "("   id_list.map((id) => "'" id"'")   ")";