In clause with a list of declared variables PostgreSQL [duplicate]

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!

Pooya · Accepted Answer · 2021-11-18 06:15:06Z

1

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[]);

edited Nov 18, 2021 at 6:15

user330315

answered Nov 18, 2021 at 6:13

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

In clause with a list of declared variables PostgreSQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related