I'm trying to create an user-defined function in Postgresql:
CREATE FUNCTION get_balance(user_id integer, statuses integer[]) RETURNS INTEGER
AS $$
select SUM(table1.credit)
from table1
inner join table2
on table2.field1 = table1.id
inner join table3
on table3.field1 = table2.id
where table3.status_id in (statuses); $$
LANGUAGE SQL;
The error is:
ERROR: operator does not exist: integer = integer[]
LINE 11: where table3.status_id in (statuses); $$
What am I doing wrong?
This:
table3.status_id in (statuses)
can be simplified for the example into:
regress=> SELECT 1 IN (ARRAY[1,2,3]);
ERROR: operator does not exist: integer = integer[]
LINE 1: SELECT 1 IN (ARRAY[1,2,3]);
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
... but IN expects a literal list, eg:
regress=> SELECT 1 IN (1, 2, 3);
Since you want to pass an array, you'll want to use = ANY(...) which expects an array input:
regress=> SELECT 1 = ANY (ARRAY[1,2,3]);
?column?
----------
t
(1 row)
