Received: from dathon.com (ideas.currentmarketing.com [216.26.151.48] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB221mm79588 for ; Sat, 1 Dec 2001 21:01:48 -0500 (EST) (envelope-from anthony@developware.com) Received: from nyartlathotep ([216.253.89.194]) by dathon.com (8.12.0/8.12.0) with SMTP id fB220msO011751 for ; Sat, 1 Dec 2001 21:00:53 -0500 From: "Anthony Bouvier" To: Subject: Unexpected Return from Function Date: Sat, 1 Dec 2001 21:00:35 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200112/2 X-Sequence-Number: 5825 I have a FUNCTION: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := responsible_list || '', '' my_record.login; END LOOP; RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; The employee table is such: id | login ------------- 1 | anthony 2 | mary ------------- I expect the SQL statement "SELECT get_responsible('1,2')" to return something like so: get_responsible --------------- anthony, mary --------------- But instead I receive: get_responsible --------------- --------------- If I get rid of the concatenation, like so: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := my_record.login; END LOOP; RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; I receive last result (for id = 2), like so: get_responsible --------------- mary --------------- The SELECT statement itself runs fine, so I know it is returning two records. Also, the reason I am passing a 'text' datatype to the function, is because I'd ultimately like to have the "WHERE id IN" statement to be dynamic, like so: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN ($1) LOOP responsible_list := my_record.login; END LOOP; RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; If anyone can help me with this, I'd be much appreciative. I've been trying combination after combination of things to try and resolve this for the past 6 and a half hours. Thanks, Anthony "pulling his hair out" Bouvier