Обсуждение: 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
============================================

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

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
> 

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


jeff=# select * from test_it;typ | diam 
-----+------01  | 80001  | 84001  | 87001  | 112002  | 76002  | 78002  | 80002  | 90003  | 120003  | 123403  | 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

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