Passing a python list into plpy execute for the in operator without string interpolation

Question

I have a plpython query for an 'in' where clause

    user_ids = [1,2,3]
    query = "SELECT department from users where id in ($1)" 
    prepared_query = plpy.prepare(query, ['bigint'])
        # not sure what the type should be if id is bigint
    plpy.execute(prepared_query, user_ids)

The problem is I am unsure what the argument type should be

I get errors for different combinations I have tried:

• When using the above syntax it was throwing an error because of the commas in the list
• when using a bigint[] I got the no operator bigint = bigint[].
• I have tried passing in a comma separated string using ",",join()
• I have cast the list to tuple as well which didn't work

Has anyone got this to work? It's poorly documented.

Answer 1

If anyone else has this problem I managed to get this fixed by switching the where clause to where id = ANY($1) and using bigint[] as the argument type