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

Question

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?

player_ids is an array variable, not literal. Use id = any(player_ids). — klin
– klin, Commented Jun 3, 2022 at 23:35
Ah, it was a problem with the function body, d'oh! I was thinking it was an issue with how I was calling the function. Thanks! — aowen
– aowen, Commented Jun 3, 2022 at 23:59

klin · Accepted Answer · 2022-06-04 10:57:05Z

2

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.

edited Jun 4, 2022 at 10:57

answered Jun 4, 2022 at 9:52

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Ramin Faracov · Accepted Answer · 2022-06-04 05:15:10Z

0

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

answered Jun 4, 2022 at 5:15

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related