Returning a Cross Tab record set from a function
От | Marc Wrubleski |
---|---|
Тема | Returning a Cross Tab record set from a function |
Дата | |
Msg-id | 1117635270.12213.49.camel@linuxtest.math.ucalgary.ca обсуждение исходный текст |
Список | pgsql-sql |
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>
В списке pgsql-sql по дате отправления: