Storing a result of a select in a variable

From: Igor Katson <descentspb(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Storing a result of a select in a variable
Date: 2009-01-23 15:48:07
Message-ID: 4979E6B7.6030209@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2009-01-23 15:53:46 Re: Using null or not null in function arguments
Previous Message Richard Huxton 2009-01-23 15:42:33 Re: how to avoid that a postgres session eats up all the memory