Re: Rewriter hook

From: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
To: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Rewriter hook
Date: 2012-12-29 03:18:05
Message-ID: 50DE60ED.2020304@dc.baikal.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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;

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2012-12-29 05:04:03 Re: enhanced error fields
Previous Message Jaime Casanova 2012-12-29 02:05:42 Re: Rewriter hook