Question 114206 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I write a database function with where in query

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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"'") + ")";

  2. 0

    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, ',')))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search