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