Multiple pass to simple parameter in Postgresql

Question

I created a function in Postgresql where I sum certain accounts. However, my function has only one parameter for them:

f_retorna_somatorio(p_conta character varying, p_cnpj character varying, p_ano_mes character varying)

The parameter "p_account", needs to receive three values: 300001, 300002, 300003

I will use this inside an IN. Whenever I select in the function it is returning me 0 because of a coalesce.

Does anyone know how to make that passage?

Note: I tried the following example already and it didn’t work:

f_retorna_somatorio($$'300001','300002','300003'$$, '00000000000000', '0000-00')

Answer 1

Change your Function to receive a string array in the parameter p_conta declaring him as p_conta character varying[]. This way you can enter multiple values in one variable.

In the SQL command in the query inside the function instead of using the IN utilize ANY, receiving the array p_conta. You did not post the function code but it would be similar to the following example:

select *
from tabela
where campo = any(p_conta);

And to execute the function just inform the similar array the following example:

select f_retorna_somatorio(array['300001','300002','300003'], '00000000000000', '0000-00');