Thread: Return relation table data in a single value CSV
I should probably be punished for even asking this question, but a simplified version of what I want is this... I have 2 tables: floorplans floorplan_id | description -------------------------- 2240 | test floorplan and a table elevations floorplan_id | elevation ------------------------ 2240 | A 2240 | B 2240 | C I want to perform a query that returns this result set: baseplan_id | elevations 2240 | A,B,C The real query is, of course, *much* more complex then that, as there are many more fields in floorplans, and floorplans is joined to other tables. Currently I return my floorplan information, then perform a second query to get the elevation records, and loop over the second query to compile the comma separated list of elevations. I have tried subselects concatenated with basically || ',' || where each subselect does an OFFSET X LIMIT 1, and the ',' is wrapped with a case statement to hide the comma if there are no further elevations. It gets very messy very fast as and further I end up hard coding the max number of elevations. Any ideas? Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085
On Tuesday 17 February 2004 20:05, terry@ashtonwoodshomes.com wrote: > I should probably be punished for even asking this question, but a > simplified version of what I want is this... > > I have 2 tables: > floorplans > floorplan_id | description > -------------------------- > 2240 | test floorplan > > and a table elevations > floorplan_id | elevation > ------------------------ > 2240 | A > 2240 | B > 2240 | C > > I want to perform a query that returns this result set: > baseplan_id | elevations > 2240 | A,B,C You've got two options here: 1. Write a set-returning function in plpgsql (or whatever) to do your looping and build the CSV value. Perhaps look in the contrib/ folder too - might be something in the tablefunc section. 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. You can find info on both in the archives, probably with examples. Also - check techdocs. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.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 ssGROUP 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
On Tuesday 17 February 2004 23:33, Tom Lane wrote: > Richard Huxton <dev@archonet.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. Hmm - good to know, but I'm always wary of doing this sort of thing. It's exactly the sort of trick I look at 18 months later, fail to read my own comments and "tidy" it. -- Richard Huxton Archonet Ltd