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>> <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:[EMAIL PROTECTED]
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
