Re: [GENERAL] Join field values
On Jun 7, 9:49 pm, [EMAIL PROTECTED] (Jerry Sievers) wrote: No sense in writing your own func for this; the feature is already provided. select array_to_string(array(select * from generate_series(1,5)), ','); Tell me about redundant efforts! :) Regards Erwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Join field values
veejar [EMAIL PROTECTED] writes: Hello! I have such field in my table: field1 --- 1 2 3 4 5 I want to get such result from select: '1,2,3,4,5' - join all values in field1 with ',' // result must be varchar. No sense in writing your own func for this; the feature is already provided. select array_to_string(array(select * from generate_series(1,5)), ','); array_to_string - 1,2,3,4,5 (1 row) Help to write SELECT-query for this task. -- --- Jerry Sievers 732 365-2844 (work) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Join field values
On Jun 5, 10:14 pm, Erwin Brandstetter [EMAIL PROTECTED] wrote: CREATE OR REPLACE FUNCTION f_concat_comma(text, text) (...) LANGUAGE 'plpgsql' STABLE IMMUTABLE; There's a typo. Should be: LANGUAGE 'plpgsql' IMMUTABLE; Regards Erwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Join field values
Hello! I have such field in my table: field1 --- 1 2 3 4 5 I want to get such result from select: '1,2,3,4,5' - join all values in field1 with ',' // result must be varchar. Help to write SELECT-query for this task.
Re: [GENERAL] Join field values
On Tue, 2007-06-05 at 19:33 +0300, veejar wrote: Hello! I have such field in my table: field1 --- 1 2 3 4 5 I want to get such result from select: '1,2,3,4,5' - join all values in field1 with ',' // result must be varchar. Look into writing a simple function: http://www.postgresql.org/docs/current/static/plpgsql.html Also, consider that you should have an ORDER BY somewhere, to make sure the values get joined in the order that you expect. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Join field values
On þri, 2007-06-05 at 19:33 +0300, veejar wrote: Hello! I have such field in my table: field1 --- 1 2 3 4 5 I want to get such result from select: '1,2,3,4,5' - join all values in field1 with ',' // result must be varchar. Help to write SELECT-query for this task. create an aggregate function and use that in your select. http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html google reminded me of the mysql compatibility project, whose implementation for group_concat() can be found here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mysqlcompat/mysqlcompat/aggregate.sql?rev=1.2content-type=text/x-cvsweb-markup gnari ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Join field values
On Jun 5, 7:39 pm, [EMAIL PROTECTED] (Ragnar) wrote: create an aggregate function and use that in your select.http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html Of course you could do that. And it would look like that: CREATE OR REPLACE FUNCTION f_concat_comma(text, text) RETURNS text AS $BODY$ BEGIN RETURN $1 || ', ' || $2; END; $BODY$ LANGUAGE 'plpgsql' STABLE IMMUTABLE; CREATE AGGREGATE concat_comma( BASETYPE=text, SFUNC=f_concat_comma, STYPE=text ); SELECT concat_comma(field1) FROM mytbl; --Or, if want the values ordered: SELECT concat_comma(field1) FROM (SELECT field1 FROM mytbl ORDER by field1) x; And that's what I did - before I found out about this simpler way: SELECT array_to_string(ARRAY(SELECT field1 FROM mytbl ORDER by field1), ', '); More info: http://www.postgresql.org/docs/8.2/static/functions-array.html Regards Erwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
