On Wed, Jun 19, 2013 at 3:56 AM, sachin kotwal <kotsachin@gmail.com> wrote:
> While migrating my application from DB2 to PostgreSQL.
>
> I want to migrate ROLLUP() function in PostgreSQL.
>
> Example:
> SELECT WEEK(SALES_DATE) AS WEEK,
> DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
> SUM(SALES) AS UNITS_SOLD
> FROM SALES
> GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )
> ORDER BY WEEK, DAY_WEEK
>
> This example shows two simple ROLLUP queries followed by a query which
> treats the two ROLLUPs as grouping sets in a single result set and specifies
> row ordering for each column involved in the grouping sets.
>
> How can I migrate ROLLUP() function in PostgreSQL? or is there any similar
> function in PostgreSQL which can done this job?
Yeah, we don't have it. To work around the problem I typically
create a function or a view dynamically, that takes a template and
forces the rollup with a UNION ALL. This technique is not generic,
so each case has to be handled specially. Lack of OLAP style sql
features is #2 missing feature for me in postgres (after stored
procedures).
merlin