Multiple pass to simple parameter in Postgresql

Asked

Viewed 415 times

1

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')
  • Tried calling with '300001,300002,300003'?

  • Another possibility is, within its function, to mount the string of its query and execute it using EXECUTE. https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

1 answer

2


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');
  • friend Gracias :)

  • Works also for numerical parameter case?

  • It does work. It’s just you set the parameter with the type integer[] for example and enter the values in select with the array constructor: array[123,456,789]

Browser other questions tagged

You are not signed in. Login or sign up in order to post.