From: Marc Wrubleski <mlwruble@math.ucalgary.ca>
To: pgsql-sql@postgresql.org
Subject: Returning a Cross Tab record set from a function
Date: Wed, 01 Jun 2005 08:14:31 -0600
Message-ID: <1117635270.12213.49.camel@linuxtest.math.ucalgary.ca> (raw)
I have read the great work that all the list members have done working
with cross tabs (pivot tables) in postgresql. The issue I have not seen
a solution for, but would really like to see, is the ability to return
the results of a dynamic (variable # of columns) cross tab function as a
recordset.
The excellent code contributed by Christoph Haller in the "Generating a
cross tab II (pivot table)" thread was very useful, but it dumps the
results into a view. I need to query like "select * from
create_pivot_report('sales_report2','vendor','product','sales','sum','sales');"
and have the result back as a recordset.
The reason I want to do this is that I have a hierarchical structure of
itemtypes where each itemtype contains an arbitrary number of items, AND
each itemtype has an arbitrary number of attributes. I want to perform
the crosstab on the items with attributes for a given itemtype. The
static code works perfectly fine for a query of an itemtype, BUT the
itemtypes and attributes may change often enough that creating views for
each itemtype will be insufficient.
It seems I can do this from any higher level language, but it drives me
crazy that I can't perform this operation as a function inside of
Postgres...
Thanks for any thoughts you might have...
--
Marc Wrubleski <mlwruble_at_math.ucalgary.ca>
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: mlwruble@math.ucalgary.ca
Subject: Re: Returning a Cross Tab record set from a function
In-Reply-To: <1117635270.12213.49.camel@linuxtest.math.ucalgary.ca>
* 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