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 fB2KjBm38897 for ; Sun, 2 Dec 2001 15:45:12 -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 fB2KhosO014982; Sun, 2 Dec 2001 15:43:55 -0500 From: "Anthony Bouvier" To: "Stephan Szabo" Cc: Subject: Re: Unexpected Return from Function Date: Sun, 2 Dec 2001 15:43:36 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" 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 In-Reply-To: <20011201182212.R74581-100000@megazone23.bigpanda.com> X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200112/10 X-Sequence-Number: 5833 That was the exact problem. Your help is much appreciated. Of course, after getting this solution working in respect to the concatenation, I realise I was going about this whole thing all wrong. I spent 6 hours trying to do the original with no luck, then came in today and created an AGGREGATE that does -exactly- what I wanted to do in the first place, and in a much simpler fashion. -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo Sent: Saturday, December 01, 2001 9:23 PM To: Anthony Bouvier Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Unexpected Return from Function On Sat, 1 Dec 2001, Anthony Bouvier wrote: > 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 > --------------- > > --------------- You probably need to initialize responsible_list to an empty string. My guess is that it starts NULL and NULL concatenated with anything is still NULL. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly