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

Question

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.

Answer 1

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.

Answer 2

You can also use a set-returning function (SRF):

CREATE OR REPLACE FUNCTION mults_of_3_5() RETURNS SETOF integer AS $$
BEGIN
  RETURN QUERY SELECT ValuesUnder1000
               FROM Mults
               WHERE (ValuesUnder1000 % 3 = 0) OR (ValuesUnder1000 % 5 = 0);
END;
$$ LANGUAGE plpgsql;

I tested it as follows and it seems it works:

CREATE TABLE Mults (ValuesUnder1000 integer);

INSERT INTO Mults (ValuesUnder1000) VALUES (3), (5), (9), (10), (12), (15);

SELECT * FROM mults_of_3_5();