Hi, On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote: > 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 ! >
As a matter of fact, it does: CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } ^ ^ | | See -----------------------------------------+------------------------ Extract from http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql