Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

You are missing a select statement

select
    id,
    array(select id_adj(id))
from existingtable
share|improve this answer
    
Thank you, that was simple! –  John Clarke 23 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.