pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStill 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