Using multi-valued parameter with IN()

Question

I'm having trouble with this postgres function using a multi-valued (comma separated) parameter as a filter in my where clause using IN().

CREATE FUNCTION company_sites_report( company_list text )
  RETURNS TABLE(company text, num_suppliers BIGINT)
LANGUAGE SQL
AS $$

SELECT company, count(supplier_id) num_suppliers
FROM MySitesTable
WHERE company IN ($1)
GROUP BY company;

$$;

So when I call the function:

SELECT * 
FROM company_sites_report( 'Company1,Company2' );

It seems to treat the parameter value as one whole string and not split it into the particular strings (csv). How do I get it to behave as I need?

try variadic?.. or plpgslq and dynamic string

Vao Tsun
– Vao Tsun

2017-05-08 10:43:56 +00:00
Commented May 8, 2017 at 10:43 — Vao Tsun
– Vao Tsun, Commented May 8, 2017 at 10:43

Vao Tsun · Accepted Answer · 2017-05-08 10:50:01Z

4

consider using VARIADIC, example:

t=# CREATE FUNCTION f1( a variadic text[] )
  RETURNS TABLE(t text)
LANGUAGE SQL
AS $$

SELECT tablename::text
FROM pg_tables
WHERE tablename = ANY ($1)
;
$$;

example:

t=# select * from f1('so4','s111','so2');
  t
------
 so2
 so4
 s111
(3 rows)

answered May 8, 2017 at 10:50

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2017-05-08 10:43:46Z

2

You are passing a string, so the condition uses it as a single value.

You need to either pass the company names as an array:

CREATE FUNCTION company_sites_report( company_list text[] )
  RETURNS TABLE(company text, num_suppliers BIGINT)
LANGUAGE SQL
AS $$

SELECT company, count(supplier_id) num_suppliers
FROM MySitesTable
WHERE company = ANY ($1)
GROUP BY company;
$$; 

select *
from company_sites_report(array['Company1', 'Company2']);

or convert the comma separated string to an array inside the procedure:

CREATE FUNCTION company_sites_report( company_list text)
  RETURNS TABLE(company text, num_suppliers BIGINT)
LANGUAGE SQL
AS $$

SELECT company, count(supplier_id) num_suppliers
FROM MySitesTable
WHERE company = ANY (string_to_array($1, ','))
GROUP BY company;
$$; 

select *
from company_sites_report('Company1,Company2');

I would prefer the first solution as you don't need to worry about company names containing the delimiter character

answered May 8, 2017 at 10:43

user330315

1 Comment

Dave B Over a year ago

Nice one, much appreciated!

Collectives™ on Stack Overflow

Using multi-valued parameter with IN()

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related