Обсуждение: Rewriter hook

Hi all,

Are there any plans on adding a rewriter hook? There are already exist 
parser, planner, executor hooks but there is no way to control rewriter 
from plugins.

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.

On 29 December 2012 01:36, Vlad Arkhipov <arhipov@dc.baikal.ru> wrote:
> Are there any plans on adding a rewriter hook?

I doubt it will ever happen.

If you look at QueryRewrite(Query *parsetree), the primary entry point
to the rewriter, it's easy enough to get a high level overview of what
goes on.

You can get the post-parse-analyze tree by registering a
post_parse_analyze_hook (like pg_stat_statements), and the same tree
*after* rule expansion by registering a planner_hook. It isn't obvious
what you're missing that a hook into the rewriter would get you.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov <arhipov@dc.baikal.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

On 12/29/2012 11:05 AM, Jaime Casanova wrote:
> On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov <arhipov@dc.baikal.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;