Postgres: calling function with text[] param fails with array literal-CodePudding

I have a Postgres function that accepts a text[] as input. For example

create function temp1(player_ids text[])
    returns void
    language plpgsql
as
$$
begin
    update players set player_xp = 0
    where id in (player_ids);
    -- the body is actually 20 lines long, updating a lot of tables
end;
$$;

and I'm trying to call it, but I keep getting

[42883] ERROR: operator does not exist: text = text[] Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Where: PL/pgSQL function temp1(text[]) line 3 at SQL statement

I have tried these so far

select temp1('{F7AWLJWYQ5BMPKGXLMDNQKQ4NY,AQPBAFKQONGLBKIMCSOD747GY4}');
select temp1('{F7AWLJWYQ5BMPKGXLMDNQKQ4NY,AQPBAFKQONGLBKIMCSOD747GY4}'::text[]);
select temp1(array['F7AWLJWYQ5BMPKGXLMDNQKQ4NY,AQPBAFKQONGLBKIMCSOD747GY4']);
select temp1(array['F7AWLJWYQ5BMPKGXLMDNQKQ4NY,AQPBAFKQONGLBKIMCSOD747GY4']::text[]);

I have to be missing something obvious...how do I call this function with an array literal?

CodePudding user response：

You can use unnest function, this function is very easy and same time best performanced. Unnest using for converting array elements to rows. Example:

create function temp1(player_ids text[])
    returns void
    language plpgsql
as
$$
begin
    update players set player_xp = 0
    where id in (select pl.id from unnest(player_ids) as pl(id));
    -- the body is actually 20 lines long, updating a lot of tables
end;
$$;

And you can easily cast array elements to another type for using unnest. Example:

update players set player_xp = 0
where id in (select pl.id::integer from unnest(player_ids) as pl(id));

CodePudding user response：

Use = any instead of in:

    ...
    update players set player_xp = 0
    where id = any(player_ids);
    ...

The IN operator acts on an explicit list of values.

expression IN (value [, ...])

When you want to compare a value to each element of an array, use ANY instead.

expression operator ANY (array expression)

Note that there are variants of both constructs for subqueries expression IN (subquery) and expression operator ANY (subquery). The first one was properly used in the other answer though a subquery seems excessive in this case.