Re: [Plproxy-users] A complex plproxy query - Mailing list pgsql-general

Marko Kreen wrote:
> On 1/22/09, Igor Katson <descentspb@gmail.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;