Subject: Returning a Cross Tab record set from a function
From: Marc Wrubleski <mlwruble@math.ucalgary.ca>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Message-Id: <1117635270.12213.49.camel@linuxtest.math.ucalgary.ca>
Mime-Version: 1.0
Date: Wed, 01 Jun 2005 08:14:31 -0600
Content-Transfer-Encoding: 7bit

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>