pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturn relation table data in a single value CSV
4+ messages / 3 participants
[nested] [flat]
* Return relation table data in a single value CSV
@ 2004-02-17 20:05 terry@ashtonwoodshomes.com
0 siblings, 1 reply; 4+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-02-17 20:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return relation table data in a single value CSV
@ 2004-02-17 21:04 Richard Huxton <dev@archonet.com>
parent: terry@ashtonwoodshomes.com
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2004-02-17 21:04 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return relation table data in a single value CSV
@ 2004-02-17 23:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-02-17 23:33 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Return relation table data in a single value CSV
@ 2004-02-18 09:29 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-02-18 09:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-18 09:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-17 20:05 Return relation table data in a single value CSV terry@ashtonwoodshomes.com
2004-02-17 21:04 ` Richard Huxton <dev@archonet.com>
2004-02-17 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-18 09:29 ` Richard Huxton <dev@archonet.com>
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