Using WHERE ... IN in a Postgres Function

Question

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?

I forgot to mention something... if you are really doing select *, then you don't need to declare the table as the output. Instead you could do RETURNS SETOF form AS instead of RETURNS TABLE ( ... ) AS. I realize this may be a primitive example to demonstrate your issue, but if you really are literally returning every field from the "form" table, this might be helpful. — Hambone
– Hambone, Commented Nov 16, 2017 at 12:53

Hambone · Accepted Answer · 2017-11-16 11:52:56Z

6

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.

answered Nov 16, 2017 at 11:52

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

2 Comments

Algy Taylor Over a year ago

"in would be for a discrete list of items, but any is used for an array" - thanks, I've learnt something new today :) will accept your answer as soon as I'm allowed

Hambone Over a year ago

I'm glad. Read up on postgresql's arrays. They are a game changer.

Collectives™ on Stack Overflow

Using WHERE ... IN in a Postgres Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related