pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tjk@tksoft.com <tjk@tksoft.com>
To: lbc@telecam.demon.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: Still don't know how to build this string ? how to concat ??
Date: Tue, 27 Mar 2001 09:10:03 -0800 (PST)
Message-ID: <200103271710.JAA17053@smtp3.tksoft.com> (raw)
In-Reply-To: <2927997826.20010327175614@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 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: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)
> 




view thread (9+ messages)

Message-ID: <200103271710.JAA17053@smtp3.tksoft.com>
Permalink:  ../200103271710.JAA17053@smtp3.tksoft.com/
Also on:    postgresql.org/message-id/200103271710.JAA17053@smtp3.tksoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tjk@tksoft.com, lbc@telecam.demon.co.uk
  Subject: Re: Still don't know how to build this string ? how to concat ??
  In-Reply-To: <200103271710.JAA17053@smtp3.tksoft.com>
  References: <2927997826.20010327175614@telecam.demon.co.uk> <200103271710.JAA17053@smtp3.tksoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox