Storing a result of a select in a variable

[Igor Katson <descentspb@xxxxxxxxx>]

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general