Re: [Plproxy-users] A complex plproxy query

Marko Kreen wrote:
> On 1/22/09, Igor Katson <descentspb(at)gmail(dot)com> wrote:
>
>> Hannu Krosing wrote:
>> > On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:
>> >
>> >
>> >> So to say, give me the list of friends (not only their ID's, but all the
>> >> needed columns!) of given individual, which are in a given group. That
>> >> seems ok without plproxy, but with using it, I can't imagine how can I
>> >> form a nice query, or a function (or a set of plpgsql + plproxy
>> >> functions) to do the job.
>> >>
>> >
>> > You need to do it in two steps - first run a query on the partition the
>> > user is in to get list of friends ids, then run a second RUN ON ALL
>> > query with
>> >
>> > WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2
>> >
>> > to gather all friend info in parallel
>> >
>> >
>>
>> I was thinking about that. But I don't understand, how can I pass the
>> list of id's. Should I turn the output of a select into an array? How
>> then? What if the array gets hundreds of items long?
>>
>
> Yes, array works fine. And if it's long, then let it be long...
>
>
Ok, thank you, guys. What is the best way to make an array out of a
column? I didn't make up anything better then writing a function:

CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
AS $$
DECLARE
arr int[];
rec int;
BEGIN
FOR rec IN EXECUTE query
LOOP
arr := array_append('{}',rec);
END LOOP;
RETURN arr;
END;
$$ language plpgsql;