From: F. BROUARD / SQLpro <sqlpro@club-internet.fr>
To: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Creating rule for sliding data
Date: Sun, 09 Oct 2011 18:50:04 +0200
Message-ID: <4E91D0BC.2030406@club-internet.fr> (raw)
In-Reply-To: <4E917601.4040700@club-internet.fr>
References: <4E917601.4040700@club-internet.fr>
I am answering to myseilf...
the good syntax is something like :
CREATE RULE R_U_MSR_BEFORE2000x
AS
ON UPDATE TO T_MESURE_MSR
WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
(
-- rows does not change partition :
UPDATE T_MESURE_BEFORE2000_MSR
SET MSR_ID = NEW.MSR_ID,
MSR_DATE = NEW.MSR_DATE,
MSR_MESURE = NEW.MSR_MESURE
WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
-- rows does change partition (first INSERT NEWs then DELETE OLDs)
INSERT INTO T_MESURE_MSR
SELECT MSR_ID,
MSR_DATE,
MSR_MESURE
FROM NEW
WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ;
DELETE FROM T_MESURE_MSR
WHERE MSR_ID = OLD.MSR_ID
AND MSR_DATE = OLD.MSR_DATE
AND MSR_MESURE = OLD.MSR_MESURE
AND NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
);
The problem is nowhere in the doc there is a mention where much more
than one commande must be place into brackets !
A +
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sqlpro@club-internet.fr
Subject: Re: [GENERAL] Creating rule for sliding data
In-Reply-To: <4E91D0BC.2030406@club-internet.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox