CODEWITHSUNDEEP
postgresqlparameter-passingfiltering
Dave B
Dave B

Reputation: 23

Using multi-valued parameter with IN()

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?

Upvotes: 2

Views: 489

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51456

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)

Upvotes: 4

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions