Steven Murdoch escreveu:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.
>
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?
>
> Thanks,
> Steven.
>
> CREATE TABLE a ( -- Names
> id INT PRIMARY KEY,
> name TEXT NOT NULL);
>
> CREATE TABLE b ( -- Codes
> id INT PRIMARY KEY,
> code CHAR(2) NOT NULL);
>
> CREATE TABLE ab ( -- m:n relationship between a and b
> id SERIAL PRIMARY KEY,
> a_id INT NOT NULL,
> b_id INT NOT NULL);
>
> COPY a(id,name) FROM STDIN DELIMITER '|';
> 1|Alice
> 2|Bob
> 3|Charlie
> \.
>
> COPY b(id, code) FROM STDIN DELIMITER '|';
> 1|a
> 2|b
> 3|c
> 4|d
> \.
>
> COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
> 2|4
> 2|1
> 3|2
> 3|3
> \.
>
> -- Custom aggregate function which concatenates strings
> CREATE AGGREGATE concat (
> BASETYPE = text,
> SFUNC = textcat,
> STYPE = text,
> INITCOND = '',
> );
>
> -- Current query
> SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
> FROM
> a LEFT JOIN ab ON (a.id=ab.a_id)
> LEFT JOIN b ON (ab.b_id=b.id)
> GROUP BY a.name
> ORDER BY codes;
>
> -- Actual output:
> --
> -- name | codes
> -- ---------+-------
> -- Alice |
> -- Charlie | b c
> -- Bob | d a
>
>
> -- Desired output:
> --
> -- name | codes
> -- ---------+--------
> -- Alice |
> -- Bob | a d
> -- Charlie | b c
>
> [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
>
Look this message:
http://archives.postgresql.org/pgsql-sql/2006-05/msg00044.php
[]s
Osvaldo
_______________________________________________________
Você quer respostas para suas perguntas? Ou você sabe muito e quer compartilhar seu conhecimento? Experimente o Yahoo!
Respostas!
http://br.answers.yahoo.com/
