Postgres: How to return an integer array from stored function

Question

I want to create a query using something like the following:

select id, array(id_adj(id)) from existingtable

which would be two columns: 1 with the id, and the 2nd column with an array of integers.

The function id_adj returns a set of rows (single column of integers) and is written as follows:

DROP FUNCTION IF EXISTS id_adj(hz_id int);
CREATE FUNCTION id_adj(id int) returns SETOF int AS $$
    select b.id
        from existingtable a, existingtable b
        where a.id != b.id
          and a.id=$1
          and ST_Distance(a.wkb_geometry, b.wkb_geometry) <= 0.05
$$LANGUAGE SQL

The above function works for a single id. For example:

select id_adj(462);

returns a single column with integer values.

I know that the array() function returns an array of values given a query result from a SELECT statement. For example:

select array(select id from existingtable where id<10);

returns an array "{6,5,8,9,7,3,4,1,2}".

But combining the two together does not seem to work. Note that although I'm using a postgis ST_Distance function above, it is not required to test a solution to my problem.

I'm also open to having the function return an array instead of a setof records, but that seemed more complicated at first.

Answer 1

You are missing a select statement

select
    id,
    array(select id_adj(id))
from existingtable