CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Incerteza
Incerteza

Reputation: 34884

Can't create a simple Postgresql function

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?

Upvotes: 0

Views: 323

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324445

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)

Upvotes: 1

Related Questions