pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Still don't know how to build this string ?
9+ messages / 6 participants
[nested] [flat]

* Still don't know how to build this string ?
@ 2001-03-26 10:12  juerg.rietmann@pup.ch
  0 siblings, 2 replies; 9+ messages in thread

From: juerg.rietmann@pup.ch @ 2001-03-26 10:12 UTC (permalink / raw)
  To: pgsql-sql

Hello there

I have still the same problem. Any help would really be appreciated !
Thanks ... jr

Is it possible (and I think it is) to do the following :

I have a table with diameters and types. I need to build a comma separated
string.

typ       diam
01        800
01        840
01        870
01        1120
02        760
02        780
02        800
02        900
03        1200
03        1234
03        1352

select diam from zylinder where typ='01'

should produce the string "800,840,870,1120"



============================================
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315
============================================





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Still don't know how to build this string ?
@ 2001-03-26 12:23  Richard Huxton <dev@archonet.com>
  parent: juerg.rietmann@pup.ch
  1 sibling, 0 replies; 9+ messages in thread

From: Richard Huxton @ 2001-03-26 12:23 UTC (permalink / raw)
  To: pgsql-sql; juerg.rietmann@pup.ch

From: <juerg.rietmann@pup.ch>

> I have a table with diameters and types. I need to build a comma separated
> string.
>
> typ       diam
> 01        800
[snip]
>
> select diam from zylinder where typ='01'
>
> should produce the string "800,840,870,1120"

Try the following as a starting point:

CREATE FUNCTION comma_join(text,text) RETURNS text AS '
BEGIN
  IF $1>\'\' AND $2>\'\' THEN
    RETURN $1 || \',\' || $2;
  ELSE
    RETURN $1 || $2;
  END IF;
END;
' LANGUAGE 'plpgsql';


CREATE AGGREGATE joinall (
  sfunc = comma_join,
  basetype = text,
  stype = text,
  initcond = ''
);


\d dia
          Table "dia"
 Attribute |  Type   | Modifier
-----------+---------+----------
 typ       | integer |
 diam      | integer |


SELECT typ,joinall(diam::text) FROM dia GROUP BY typ;
 typ |          joinall
-----+----------------------------
   1 | 800,840,870,1120
   2 | 760,800,900,1200,1234,1352
(2 rows)


Note the explicit cast of diam into text.

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Still don't know how to build this string ?
@ 2001-03-26 14:49  Michael Fork <mfork@toledolink.com>
  parent: juerg.rietmann@pup.ch
  1 sibling, 0 replies; 9+ messages in thread

From: Michael Fork @ 2001-03-26 14:49 UTC (permalink / raw)
  To: juerg.rietmann@pup.ch; +Cc: pgsql-sql

The following function will convert a given list into a comma delimited
string (the argument should be the typ):

CREATE FUNCTION dima_list(int4) RETURNS text AS '
        DECLARE
                rec            record;
                list           text;
        BEGIN
                list := '''';
                FOR rec IN SELECT diam FROM zylinder WHERE typ = $1
                        list := list || rec.diam || ''; ''
                END LOOP;
                RETURN list;
        END;
' LANGUAGE 'plpgsql';

Note that PL/PGSQL must be installed first, which can be done by typing

createlang plpgsql

at a shell prompt as a postgres super user.

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Mon, 26 Mar 2001 juerg.rietmann@pup.ch wrote:

> Hello there
> 
> I have still the same problem. Any help would really be appreciated !
> Thanks ... jr
> 
> Is it possible (and I think it is) to do the following :
> 
> I have a table with diameters and types. I need to build a comma separated
> string.
> 
> typ       diam
> 01        800
> 01        840
> 01        870
> 01        1120
> 02        760
> 02        780
> 02        800
> 02        900
> 03        1200
> 03        1234
> 03        1352
> 
> select diam from zylinder where typ='01'
> 
> should produce the string "800,840,870,1120"
> 
> 
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: Still don't know how to build this string ?
@ 2001-03-26 17:02  Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 9+ messages in thread

From: Jeff Eckermann @ 2001-03-26 17:02 UTC (permalink / raw)
  To: 'juerg.rietmann@pup.ch' <juerg.rietmann@pup.ch>; pgsql-sql

Still learning this stuff, so please be gentle...


jeff=# select * from test_it;
 typ | diam 
-----+------
 01  | 800
 01  | 840
 01  | 870
 01  | 1120
 02  | 760
 02  | 780
 02  | 800
 02  | 900
 03  | 1200
 03  | 1234
 03  | 1352
(11 rows)

jeff=# \! cat test_it
drop function test_it_too(text);
create function test_it_too(text)
returns text as '
declare 
typ2 alias for $1;
rec record;
string text:= '''';
begin
for rec in select * from test_it where typ = typ2 loop
string := string || rec.diam || '','';
end loop;
string := substr(string, 1, length(string)-1);
return string;
end;
' language 'plpgsql';

jeff=# \i test_it
DROP
CREATE
jeff=# select test_it_too('01');
   test_it_too    
------------------
 800,840,870,1120
(1 row)



> -----Original Message-----
> From:	juerg.rietmann@pup.ch [SMTP:juerg.rietmann@pup.ch]
> Sent:	Monday, March 26, 2001 4:13 AM
> To:	pgsql-sql@postgresql.org
> Subject:	Still don't know how to build this string ?
> 
> Hello there
> 
> I have still the same problem. Any help would really be appreciated !
> Thanks ... jr
> 
> Is it possible (and I think it is) to do the following :
> 
> I have a table with diameters and types. I need to build a comma separated
> string.
> 
> typ       diam
> 01        800
> 01        840
> 01        870
> 01        1120
> 02        760
> 02        780
> 02        800
> 02        900
> 03        1200
> 03        1234
> 03        1352
> 
> select diam from zylinder where typ='01'
> 
> should produce the string "800,840,870,1120"
> 
> 
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Still don't know how to build this string ? how to concat  ??
@ 2001-03-27 09:20  juerg.rietmann@pup.ch
  0 siblings, 1 reply; 9+ messages in thread

From: juerg.rietmann@pup.ch @ 2001-03-27 09:20 UTC (permalink / raw)
  To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org


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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Still don't know how to build this string ?
@ 2001-03-27 09:35  juerg.rietmann@pup.ch
  0 siblings, 0 replies; 9+ messages in thread

From: juerg.rietmann@pup.ch @ 2001-03-27 09:35 UTC (permalink / raw)
  To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org


Hey folk's

Thanks everybody helping me with my problem, it is solved ! The problem was
that I took double quotes instead of single quotes ... arghhhh.

By the way, is there any doc's about plpgsql ?

jr
============================================
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315
============================================




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Re: Still don't know how to build this string ? how to concat ??
@ 2001-03-27 14:22  Andy Corteen <lbc@telecam.demon.co.uk>
  parent: juerg.rietmann@pup.ch
  0 siblings, 1 reply; 9+ messages in thread

From: Andy Corteen @ 2001-03-27 14:22 UTC (permalink / raw)
  To: juerg.rietmann@pup.ch; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Re: Still don't know how to build this string ? how to concat ??
@ 2001-03-27 16:56  Andy Corteen <lbc@telecam.demon.co.uk>
  parent: Andy Corteen <lbc@telecam.demon.co.uk>
  0 siblings, 1 reply; 9+ messages in thread

From: Andy Corteen @ 2001-03-27 16:56 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Re: Still don't know how to build this string ? how to concat ??
@ 2001-03-27 17:10  tjk@tksoft.com <tjk@tksoft.com>
  parent: Andy Corteen <lbc@telecam.demon.co.uk>
  0 siblings, 0 replies; 9+ messages in thread

From: tjk@tksoft.com @ 2001-03-27 17:10 UTC (permalink / raw)
  To: lbc@telecam.demon.co.uk; +Cc: 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 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)
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2001-03-27 17:10 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 10:12 Still don't know how to build this string ? juerg.rietmann@pup.ch
2001-03-26 12:23 ` Richard Huxton <dev@archonet.com>
2001-03-26 14:49 ` Michael Fork <mfork@toledolink.com>
2001-03-26 17:02 RE: Still don't know how to build this string ? Jeff Eckermann <jeckermann@verio.net>
2001-03-27 09:20 Re: Still don't know how to build this string ? how to concat  ?? juerg.rietmann@pup.ch
2001-03-27 14:22 ` Re: Re: Still don't know how to build this string ? how to concat ?? Andy Corteen <lbc@telecam.demon.co.uk>
2001-03-27 16:56   ` Re: Re: Still don't know how to build this string ? how to concat ?? Andy Corteen <lbc@telecam.demon.co.uk>
2001-03-27 17:10     ` Re: Re: Still don't know how to build this string ? how to concat ?? tjk@tksoft.com <tjk@tksoft.com>
2001-03-27 09:35 Re: Still don't know how to build this string ? juerg.rietmann@pup.ch

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