Using WHERE ... IN in a Postgres Function

user_1842033 0
Viewed 358

I want to create a function in Postgres along the lines of this:

CREATE OR REPLACE FUNCTION public.getAvailableForms (
    get_form_names  TEXT[]
) RETURNS TABLE (
    id          INTEGER,
    name        TEXT,
    location    TEXT,
    created     TIMESTAMP
) AS $$
BEGIN
    RETURN QUERY
        SELECT *
          FROM form
         WHERE form.name IN get_form_names;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER;

However, it tells me that WHERE form.name IN get_form_names is syntactically incorrect.

I can't find any documentation on how to use array variables in a postgres function call.

Anyway, is it possible to use an array value passed as a function argument in a WHERE ... IN?

plpgsql postgresql sql
1 Answers

Use any:

where form.name = any (get_form_names)

in would be for a discrete list of items, but any is used for an array.

user_10018840
Related