Thread: Storing a result of a select in a variable

As I cannot do usual joins while using plproxy, there is a need to
perform a following set of operations:

get some (3 in fact) result columns from one function, then provide one
of this columns to another function as an array, and join the result of
the first function with the result of the second function. I don't like
the solution, that I made up, cause it executes one of the functions 2
times, once to get an array from it, and the other to make a join.

This happens cause I haven't found a way to keep the whole result of a
function (several columns) in one variable in PL/pgsql.

Is there a way to make it better? Maybe using more-than-one-dimensional
arrays or something? Which one would be more effective?

I marked the places, where the function gets called twice.

Thanks in advance.

CREATE OR REPLACE FUNCTION friend_func.get_friends(i_user_id int,
limit_ int, offset_ int) RETURNS SETOF friend_func.user_friend_full AS $$
    DECLARE
        arr int[];
        rec friend_func.user_friend_full;
    BEGIN
 >>        arr := ARRAY(SELECT friend_id FROM
friend_func.get_friends_short(i_user_id,
        limit_, offset_));
        FOR rec IN SELECT a.id,
                          b.creation_ts AS fr_creation_ts,
                          b.group_id,
                          b.alias,
                          a.nickname,
                          a.phone_number,
                          a.creation_ts AS usr_creation_ts,
                          a.passwd,
                          a.login_enabled,
                          a.city_id,
                          a.edu_id,
                          a.firstname,
                          a.lastname,
                          a.is_male,
                          a.current_status
                   FROM isocial_user_func.get_users_from_array(arr) a,
 >>                        friend_func.get_friends_short(i_user_id,
                        limit_, offset_) b
                   WHERE a.id = b.friend_id
        LOOP
            RETURN NEXT rec;
        END LOOP;
        RETURN;
    END;
$$ language plpgsql;

Regards,
Igor Katson