CODEWITHSUNDEEP
postgresql
Richie Cotton
Richie Cotton

Reputation: 121077

Filtering on multiple values in function

The non-functional version of my problem is easy: I want to filter values based upon whether a text variable has some specific values:

SELECT routine_name, data_type
  FROM information_schema.routines
   WHERE data_type IN ('boolean', 'integer');

Since I want to do many variations on what I'm filtering on, I want to have a function that accepts the values to filter on. My attempt at turning this into a function looks like this:

CREATE FUNCTION get_fns_by_data_type(data_types text[])
  RETURNS TABLE(routine_name text, data_type text) AS $$
BEGIN
  RETURN QUERY SELECT routine_name, data_type
    FROM information_schema.routines
    WHERE data_type IN (array_to_string($1, ','));
END;
$$ LANGUAGE plpgsql;

When I call it:

SELECT * FROM get_fns_by_data_type(ARRAY['boolean', 'integer'])

I get no results.

I suspect that somehow I should be quoting the values, but I'm not sure of the best approach to this, nor how to debug the problem.

How do I use the array in my WHERE clause?

Upvotes: 0

Views: 2513

Answers (1)

user330315
user330315

Reputation:

array_to_string returns a single string, not a list of string, so in your function you are actually running:

where data_type IN ('boolean, integer')

(which is clearly not what you intended)

You don't need convert the array in the first place. You can use it directly 

where data_type = any ($1)

Upvotes: 2

Related Questions