Help function to sort string

Le mardi 15 octobre 2013 ￜ 08:52 -0700, ginkgo36 a ￜcrit :

1. I want to sort string follow anphabet and I used this query:
select string_agg(x, ';') from (select
trim(unnest(regexp_split_to_array('ECD FORM; BODY; PREDILUTED; CHROMO-GENIC;
AUTO;RABBIT; FORMAT',';'))) x order by x) a;

-- result: AUTO; BODY; CHROMOGENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
-->I expected this rusult

In my database I have a column with alot of rows data. I want that query
become a function to more easy to using. But I can not write a function :(.
please hepl me.
For example, I have column "data_text" with data like this:
Row 1: AUTO; BODY; PREDILUTED; ECD FORM; RABBIT; FORMAT; CHROMOGENIC
Row 2: ECD FORM; BODY; PREDILUTED; CHROMO-GENIC; AUTO; RABBIT; FORMAT
Row 3: FORMAT; ECD FORM; AUTO
Row 3: ANHYDROUS; DENATURED; PREDILUTED; CHROMOGENIC

When I run funtion, the result:
Row 1: AUTO; BODY; CHROMOGENIC; ECD.FORM; FORMAT; PREDILUTED; RABBIT
Row 2: AUTO; BODY; CHROMO-GENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
Row 3: AUTO; ECD FORM; FORMAT
Row 4: ANHYDROUS; CHROMOGENIC; DENATURED; PREDILUTED

This works :

CREATE TABLE foo (id serial, data_text text);

INSERT INTO foo (data_text) values ('AUTO; BODY; PREDILUTED; ECD FORM;
RABBIT; FORMAT; CHROMOGENIC'), ('ECD FORM; BODY; PREDILUTED;
CHROMO-GENIC; AUTO; RABBIT; FORMAT'), ('FORMAT; ECD FORM; AUTO');

WITH t1 AS (SELECT id, unnest(string_to_array(data_text, '; ')) FROM foo
ORDER BY 1, 2),
t2 AS (SELECT id, array_agg(unnest) over (PARTITION BY id) AS reordered
FROM t1)
SELECT t2.id, array_to_string(t2.reordered, '; ') FROM t2 GROUP BY id,
reordered;

Result :

id | array_to_string
----+----------------------------------------------------------------
1 | AUTO; BODY; CHROMOGENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
2 | AUTO; BODY; CHROMO-GENIC; ECD FORM; FORMAT; PREDILUTED; RABBIT
3 | AUTO; ECD FORM; FORMAT

--
Salutations, Vincent Veyron

http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general