Re: Re: Still don't know how to build this string ? how to concat ?? - Mailing list pgsql-sql

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)
>