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.