Re: Rewriter hook

On 12/29/2012 11:05 AM, Jaime Casanova wrote:
> On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> wrote:
>> Some use cases:
>> 1. Complex rules in C language.
>> 2. Transforming an original query into a series of queries. For example,
>> instead of UPDATE query on a table you may wish to execute UPDATE and INSERT
>> into *the same* table.
>>
> the second one you can do it with a trigger, and i'm pretty sure you
> can use triggers to solve most of the problems... what are you trying
> to do?
>
> --
> Jaime Casanova www.2ndQuadrant.com
> Professional PostgreSQL: Soporte 24x7 y capacitación
> Phone: +593 4 5107566 Cell: +593 987171157
>

I'm trying to make an extension that rewrites UPDATE/DELETE queries to
specific tables using some additional information via special functions
into a query (in order to not extend the parser with special syntax).
For example, a query

UPDATE my_table
SET val = 2
WHERE id = 1
AND special_func(daterange('2000-02-01', '2000-03-01'));

needs to be rewritten into the following three queries:

UPDATE my_table SET val = 2
WHERE id = 1
AND period <@ daterange('2000-02-01', '2000-03-01');

UPDATE my_table SET period = period - daterange('2000-02-01', '2000-03-01')
WHERE id = 1
AND period && daterange('2000-02-01', '2000-03-01')
AND (period &> daterange('2000-02-01', '2000-03-01')
OR period &< daterange('2000-02-01', '2000-03-01'));

INSERT INTO my_table (id, val, period)
SELECT 1, 2, period * daterange('2000-02-01', '2000-03-01')
FROM my_table
WHERE id = 1
AND period && daterange('2000-02-01', '2000-03-01')
AND (NOT period &> daterange('2000-02-01', '2000-03-01')
OR NOT period &< daterange('2000-02-01', '2000-03-01'));

Here is the same query with special syntax (SQL-2011 variant):

UPDATE my_table FOR PORTION OF period_name FROM '2000-02-01' TO '2000-03-01'
SET val = 2
WHERE id = 1;