In clause with a list of declared variables PostgreSQL

Question

I have this:

CREATE or REPLACE FUNCTION name_list(name_of_interest text)
RETURNS table (name text,
               sex text,
               age int,
               state text)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN query
SELECT name, sex, age, state FROM employee
INNER JOIN address_book ON employee.id = address_book.id
WHERE name = name_of_interest;

And then run the function by determining name_of_interest, like this:

SELECT * FROM name_list('Harry Potter');

This works fine if only I am interested a single name, but if I want to replace name_of_interest to a list of name i.e('Harry Potter', 'Brad Pitt', 'Stephen Curry') and changing WHERE clause to WHERE name in (name_of_interest), then this function does no longer work.

Any comments or ideas are appreciated!

score 1 · Accepted Answer · edited Nov 18 '21 at 06:15

You use array format and send to query:

CREATE or REPLACE FUNCTION name_list(name_of_interest text[])
RETURNS table (name text,
               sex text,
               age int,
               state text)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN query
   SELECT name, sex, age, state FROM employee
     INNER JOIN address_book ON employee.id = address_book.id
   WHERE name = any(name_of_interest);
END;
$$;

Then run query like below:

SELECT * FROM name_list(array['Harry Potter', 'Brad Pitt', 'Stephen Curry']::text[]);

In clause with a list of declared variables PostgreSQL

1 Answers1