How can I write a database function with where in query

Question

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[],

Are you calling this query from JavaScript or directly from Postgres? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 17, 2022 at 7:23
You should add the relevant JS code. Just showing plain SQL won't get you an answer here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 17, 2022 at 7:31
I just want to learn the correct syntax of middle line of my sql. I am sending an array of id's and I want to get items with their id in it. Js code is irrelevant with my question. — Coşkun
– Coşkun, Commented Oct 17, 2022 at 7:34
You can't do this from native SQL like this. However, you can build a statement from JS and then bind an array of values. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 17, 2022 at 7:38

Stefanov.sm · Accepted Answer · 2022-10-17 08:31:09Z

2

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)

to

(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, ',')))

edited Oct 17, 2022 at 8:31

answered Oct 17, 2022 at 7:51

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Coşkun · Accepted Answer · 2022-10-17 08:11:55Z

0

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"'") + ")";

answered Oct 17, 2022 at 8:11

Coşkun

476 bronze badges

Collectives™ on Stack Overflow

How can I write a database function with where in query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related