Message-ID: <4E91D0BC.2030406@club-internet.fr>
Date: Sun, 09 Oct 2011 18:50:04 +0200
From: "F. BROUARD / SQLpro" <sqlpro@club-internet.fr>
Organization: sqlpro.developpez.com
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Creating rule for sliding data
References: <4E917601.4040700@club-internet.fr>
In-Reply-To: <4E917601.4040700@club-internet.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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 *************************