how to correctly pass arrays from Django/Python to Postgres/PLPGSQL stored proc variadic parameter

Question

I am trying to call a stored proc on Postgres/PLPGSQL from Django/Python. I have the stored proc defined using a VARIADIC parameter:

CREATE OR REPLACE FUNCTION udf_getmultiplecategoriescodetypes (VARIADIC NUMERIC[])

then the only place I want to use the array of parameters in the proc is in the WHERE stmt:

WHERE cct.code_category_fk_id = ANY($1)

All this works perfectly when I call the function from the DBeaver console:

SELECT * FROM udf_getmultiplecategoriescodetypes(1, 2)

However, if I use the callproc function in Django/Python, using the same type of syntax, like this:

c.callproc("udf_getmultpilecategoriescodetypes", (1, 2))

I get errors:

LINE 1: SELECT * FROM udf_getmultpilecategoriescodetypes(1,2)
HINT:  No function matches the given name and argument types. You might need to add
explicit type casts.

function udf_getmultpilecategoriescodetypes(integer, integer) does not exist

Furthermore, in DBeaver, when the function is created and stored in the functions listing, if I try to delete it, it says the function cannot be found.

Function Showing in TreeView

Deletion Error Msg

I've since found out that I can delete it by using DROP FUNCTION and including the VARIADIC parameter so it recognises it based on number and type of parameters. But why is it like that?

So, two questions:

What is the correct way to pass an array of integers from a Django/Python callproc function to a VARIADIC parameter in a Postgres/PLPGSQL stored proc?
Why does DBeaver not recognise a listed stored proc function as existing when an array or VARIADIC is used as the parameter? And might this somehow be related to the callproc error, since the errors of both issues seem to be related to the VARIADIC parameter?

Erwin Brandstetter · Accepted Answer · 2019-02-04T15:46:31.150

What is the correct way to pass an array of integers from a Django/Python callproc function to a VARIADIC parameter in a Postgres/PGPLSQL stored proc?

You defined the parameter as VARIADIC NUMERIC[], so you really want to pass an array of numeric, not an array on integer.

And since it's a VARIADIC function, you can pass a list of numeric values instead of an actual array - like you do. See:

Pass multiple values in single parameter

But that's not the problem at hand. Postgres function type resolution will fall back to func(numeric[]) if there is no func(int[]). Seems to be a plain typo. Do you see the difference?

udf_getmultiplecategoriescodetypes
udf_getmultpilecategoriescodetypes

Conciser names and maybe some underscores might help prevent such typos.

Why does DBeaver not recognise a listed stored proc function as existing when an array or VARIADIC is used as the parameter? And might this somehow be related to the callproc error, since the errors of both issues seem to be related to the VARIADIC parameter?

Postgres allows function overloading. Hence, the function signature is comprised of its name and parameters to be unambiguous. DBeaver has nothing to do with it.

On top of it, be aware that the same function can exist in multiple schemas. So make sure you operate with the right search_path and don't have (and inadvertently call) a copy in another schema.

So your attempt to drop the function public.udf_getmultiplecategoriescodetypes() fails due to the missing parameter. And it may also fail if the function was created in a different schema.

how to correctly pass arrays from Django/Python to Postgres/PLPGSQL stored proc variadic parameter

1 Answers1