On 12/29/2012 11:05 AM, Jaime Casanova wrote:
On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov <arhi...@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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
