Skip site navigation (1) Skip section navigation (2)

Re: Return relation table data in a single value CSV

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: terry(at)ashtonwoodshomes(dot)com,"Postgresql Sql Group (E-mail)" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Return relation table data in a single value CSV
Date: 2004-02-17 23:33:54
Message-ID: 18788.1077060834@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Richard Huxton <dev(at)archonet(dot)com> writes:
> 2. Write a custom aggregate function (like sum()) to do the concatenation. 
> This is easy to do, but the order your ABC get processed in is undefined.

Actually, as of 7.4 it is possible to control the order of inputs to a
custom aggregate.  You do something like this:

	SELECT foo, myagg(bar) FROM
	  (SELECT foo, bar FROM table ORDER BY foo, baz) AS ss
	GROUP BY foo

The inner sub-select must order by the columns that the outer will group
on; it can then order by additional columns that determine the sort
order within each group.  Here, myagg() will see its input ordered by
increasing values of baz.

Before 7.4 this method didn't work because the planner was too stupid to
avoid re-sorting the subquery output.  You could only make it work in
cases where you weren't doing grouping ...

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-02-17 23:40:49
Subject: Re: CHAR(n) always trims trailing spaces in 7.4
Previous:From: eleinDate: 2004-02-17 23:17:28
Subject: Re: CHAR(n) always trims trailing spaces in 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group