Received: from finch-post-12.mail.demon.net (finch-post-12.mail.demon.net [194.217.242.41]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2RFuaw21785 for ; Tue, 27 Mar 2001 10:56:36 -0500 (EST) (envelope-from lbc@telecam.demon.co.uk) Received: from telecam.demon.co.uk ([194.222.201.144] helo=linux.telecam) by finch-post-12.mail.demon.net with esmtp (Exim 2.12 #1) id 14hvpa-0001EH-0C for pgsql-sql@postgresql.org; Tue, 27 Mar 2001 15:56:26 +0000 Received: from andy.telecam (andy.telecam [10.0.0.2]) by linux.telecam (8.9.3/8.8.7) with ESMTP id QAA00617 for ; Tue, 27 Mar 2001 16:55:41 +0100 Date: Tue, 27 Mar 2001 17:56:14 +0100 From: Andy Corteen X-Mailer: The Bat! (v1.49) Business Reply-To: Andy Corteen Organization: Telecam Electronics Ltd X-Priority: 3 (Normal) Message-ID: <2927997826.20010327175614@telecam.demon.co.uk> To: pgsql-sql@postgresql.org Subject: Re: Re: Still don't know how to build this string ? how to concat ?? In-reply-To: <14918780083.20010327152237@telecam.demon.co.uk> References: <14918780083.20010327152237@telecam.demon.co.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/552 X-Sequence-Number: 2222 Hello Andy, Tuesday, March 27, 2001, 3:22:37 PM, you wrote: AC> Tuesday, March 27, 2001, 10:20:18 AM, you wrote: jrpc>> jrpc>> Result: jrpc>> 01 1440 jrpc>> 02 1460 jrpc>> 03 1398 jrpc>> The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ. jrpc>> This is correct since in the function the list:= ... is overwritten until jrpc>> the last record is read. jrpc>> When I try to concat the list in the manner of list := list || jrpc>> text(rec.z_u_umfang); the zustring is empty ! jrpc>> Thanks for any help ... jr jrpc>> Query : jrpc>> select distinct z_u_typ, buildString(z_u_typ) as zustring from jrpc>> zylinder_umfang jrpc>> Function: jrpc>> CREATE FUNCTION buildString(bpchar) RETURNS text AS ' jrpc>> DECLARE jrpc>> list text; jrpc>> rec record; jrpc>> BEGIN jrpc>> FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ jrpc>> = $1; jrpc>> list := text(rec.z_u_umfang); jrpc>> END LOOP; jrpc>> RETURN list; jrpc>> END; jrpc>> ' LANGUAGE 'plpgsql'; AC> You seem to be constantly re-assigning "list", rather than adding to AC> it with each iteration of the "for loop". AC> Would: AC> ... AC> list := list || ',' || text(rec.z_u_umfang) AC> ... AC> be what your solution is missing? I read it again and noticed your comment about having tried || already - I must learn to read messages fully... But, I did wonder if the semicolon ";" at the end of the for loop is what is causing your problem? The syntax explanation I have does not show the ";", therefore it is possible that the loop is executing a null instruction ";", moving on the the list assignment, and then finding the unmatched "end loop" which might not throw an error. Can anyone comment if this is a plausible explanation? -- Best regards, Andy mailto:lbc@telecam.demon.co.uk