Skip site navigation (1) Skip section navigation (2)

Adding rewrite rule to QueryRewrite

From: Andreas Heinisch <andreas(dot)heinisch(at)yahoo(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Adding rewrite rule to QueryRewrite
Date: 2013-01-02 14:27:40
Message-ID: 1357136860518-5738481.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

we are trying to integrate GMDA algorithm into postgresql. The operater
decouples grouping from
aggregation and we came up with the following syntax:

SELECT d, u, COUNT(*b.u <= r.u*) AS ccu, COUNT(*b.d <= r.d*) AS ccd
FROM b *GMDJOIN* r
GROUP BY d, u

This select query should be rewritten to:

WITH xi AS (
  SELECT d, u
    SUM(COUNT(*)) OVER (PARTITION BY u) AS ccu,
    SUM(COUNT(*)) OVER (PARTITION BY d) AS ccd
  FROM r
  GROUP BY d, u
),
x1 AS (
  SELECT b1.u, SUM(ccu) AS ccu
  FROM
    (SELECT DISTINCT u FROM xi) b1
    LEFT OUTER JOIN
    (SELECT DISTINCT u, ccu FROM xi) xi1
    ON xi1.u = b1.u
  GROUP BY b1.u
),
x2 AS (
  SELECT b2.d, SUM(ccd) AS ccd
  FROM
    (SELECT DISTINCT d FROM xi) b2
    LEFT OUTER JOIN
    (SELECT DISTINCT d, ccd FROM xi) xi2
    ON xi2.d = b2.d
  GROUP BY b2.d
)
SELECT *
FROM x1 NATURAL JOIN x2
ORDER BY d,u

Now to our question:
Can this be integrated into the postresql QueryRewrite function? Should that
be integrated into the rewrite stage of the postgresql kernel or should the
rewrite action be done somewhere else? Is there any tutorial on rewriting
statements?

Thank you very much for your time and help,
Andi



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Adding-rewrite-rule-to-QueryRewrite-tp5738481.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


pgsql-hackers by date

Next:From: Claudio FreireDate: 2013-01-02 14:32:50
Subject: Re: default SSL compression (was: libpq compression)
Previous:From: Magnus HaganderDate: 2013-01-02 14:17:14
Subject: Re: default SSL compression (was: libpq compression)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group