Thread: Re: Still don't know how to build this string ? how to concat ??

Re: Still don't know how to build this string ? how to concat ??

From
juerg.rietmann@pup.ch
Date:
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
============================================



Re: Re: Still don't know how to build this string ? how to concat ??

From
Andy Corteen
Date:
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




Re: Re: Still don't know how to build this string ? how to concat ??

From
Andy Corteen
Date:
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




Re: Re: Still don't know how to build this string ? how to concat ??

From
"tjk@tksoft.com"
Date:
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)
>