From: Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
To: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Sorting items in aggregate function
Date: Tue, 12 Sep 2006 17:54:12 -0300
Message-ID: <45071E74.3030807@yahoo.com.br> (raw)
In-Reply-To: <20060912154628.GB4991@cl.cam.ac.uk>
References: <20060912154628.GB4991@cl.cam.ac.uk>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: osvaldo_kussama@yahoo.com.br, psql+Steven.Murdoch@cl.cam.ac.uk
Subject: Re: Sorting items in aggregate function
In-Reply-To: <45071E74.3030807@yahoo.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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