Обсуждение: Migration from DB2 to PostgreSQL-ROLLUP()

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?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-ROLLUP-tp5759825.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hello

2013/6/19 sachin kotwal <kotsachin@gmail.com>:
> 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?

this functionality is not supported

you should to rewrite query

SELECT .. FROM GROUP BY weeks(sales_date), dayofweek(salesdate) UNION
ALL SELECT ... FROM .. GROUP BY dayofweek(salesdate) ORDER BY ..

Regards

Pavel Stehule

>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-ROLLUP-tp5759825.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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