[SOLVED] How to use a query returning integers in an SQL function that returns integer[]?

How to use a query returning integers in an SQL function that returns integer[]?

I created a function that returns all values that meet the condition specified and tried specifying int[] as the result type of the function.

create or replace function mults_of_3_5(id int) returns int[] language sql as
$$
SELECT ValuesUnder1000
FROM Mults
WHERE (ValuesUnder1000 % 3 = 0) or (ValuesUnder1000 % 5 = 0);
$$;

I got the error

ERROR:  return type mismatch in function declared to return integer[]
DETAIL:  Actual return type is integer.
CONTEXT:  sql function "mults_of_3_5"

The data type of ValuesUnder1000 is integer.

When I use the same query outside the function, all the values in the column that satisfies the condition are listed.

Please tell me what I can do to return an array in PostgreSQL.

Solution

Use pg_typeof to check your data types:

select valuesunder1000, pg_typeof(valuesunder1000)
from mults
where (valuesunder1000 % 5 = 0) or (valuesunder1000 % 3 = 0);

Your function returns setof int. If you want it to return an int[] array instead, you need to assemble that array. array_agg() can help

create or replace function mults_of_3_5 (id int) returns int[] as $f$
select array_agg(valuesunder1000)
from mults
where (valuesunder1000 % 5 = 0) or (valuesunder1000 % 3 = 0);
$f$ language sql;

It's also possible to use returns table (valuesunder1000 int). Refer to the manual for details on how to build functions.