Thread: Re: Still don't know how to build this string ? how to concat ??
Hello I'm closer to a solution. The query results is : Result: 01 1440 02 1460 03 1398 The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ. This is correct since in the function the list:= ... is overwritten until the last record is read. When I try to concat the list in the manner of list := list || text(rec.z_u_umfang); the zustring is empty ! Thanks for any help ... jr Query : select distinct z_u_typ, buildString(z_u_typ) as zustring from zylinder_umfang Function: CREATE FUNCTION buildString(bpchar) RETURNS text AS ' DECLARE list text; rec record; BEGIN FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ = $1; list := text(rec.z_u_umfang); END LOOP; RETURN list; END; ' LANGUAGE 'plpgsql'; ============================================ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ============================================
Tuesday, March 27, 2001, 10:20:18 AM, you wrote: jrpc> <snip...> 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'; You seem to be constantly re-assigning "list", rather than adding to it with each iteration of the "for loop". Would: ... list := list || ',' || text(rec.z_u_umfang) ... be what your solution is missing? -- Best regards,Andy mailto:lbc@telecam.demon.co.uk
Hello Andy, Tuesday, March 27, 2001, 3:22:37 PM, you wrote: AC> Tuesday, March 27, 2001, 10:20:18 AM, you wrote: jrpc>> <snip...> 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
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
ANDNOT 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>> <snip...>
>
> 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)
>