DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
	b=JnR4HqJaIRqJFglcNzzbV429e2KF+OtkW9QUQvMQ+/hkbZxwuYOYBzaf58YuTgMuT6ifJ4nUJyZ0CUUnLExUzb8uL/nyX+uFgma6fGp0FrwGqaoS3BqglSycDq4fjdMFecBLcbwxF5SyM2jG80bLnOw+65Zbo6uBpb8nSSAU97I=
	;
Message-ID: <45071E74.3030807@yahoo.com.br>
Date: Tue, 12 Sep 2006 17:54:12 -0300
From: Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
User-Agent: Thunderbird 1.5.0.4 (X11/20060614)
MIME-Version: 1.0
To: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>,
	pgsql-sql@postgresql.org
Subject: Re: Sorting items in aggregate function
References: <20060912154628.GB4991@cl.cam.ac.uk>
In-Reply-To: <20060912154628.GB4991@cl.cam.ac.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/