Received: from gyw.com (gyw.com [209.55.67.177]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2RH3kw37327 for ; Tue, 27 Mar 2001 12:03:46 -0500 (EST) (envelope-from tjk@tksoft.com) Received: from smtp3.tksoft.com (smtp3.tksoft.com [192.168.50.56] (may be forged)) by gyw.com (8.8.8/8.8.8) with ESMTP id JAA20059; Tue, 27 Mar 2001 09:23:06 -0800 Received: (from tjk@tksoft.com) by smtp3.tksoft.com (8.8.8/8.8.8) id JAA17053; Tue, 27 Mar 2001 09:10:04 -0800 From: "tjk@tksoft.com" Message-Id: <200103271710.JAA17053@smtp3.tksoft.com> Subject: Re: Re: Still don't know how to build this string ? how to concat ?? To: lbc@telecam.demon.co.uk Date: Tue, 27 Mar 2001 09:10:03 -0800 (PST) Cc: pgsql-sql@postgresql.org In-Reply-To: <2927997826.20010327175614@telecam.demon.co.uk> from "Andy Corteen" at Mar 27, 2001 05:56:14 PM X-Info: None MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/553 X-Sequence-Number: 2223 If I understand corrently, the idea is to get a comma delimited list as a result. here is a modified function with a slightly different set of names for the table. The commands include commands to add and drop the needed tables. CREATE TABLE emps (username text, userid int4); INSERT INTO emps VALUES ('User 1', 1); INSERT INTO emps VALUES ('User X', 2); INSERT INTO emps VALUES ('User 2', 2); INSERT INTO emps VALUES (null, 2); INSERT INTO emps VALUES ('something', null); CREATE FUNCTION com_delim(int4) RETURNS text AS ' DECLARE rec record; str text; comstr text; BEGIN str := ''''; comstr := ''''; FOR rec IN SELECT username FROM emps WHERE userid = $1 AND NOT username ISNULL LOOP str := str || comstr || rec.username; comstr := '',''; END LOOP; RETURN str; END; ' LANGUAGE 'plpgsql'; SELECT com_delim(2) FROM emps; DROP FUNCTION com_delim(int4); DROP TABLE emps; Troy > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >