From: Guillaume Lelarge <guillaume@lelarge.info>
To: F. BROUARD / SQLpro <sqlpro@club-internet.fr>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: [GENERAL] Creating rule for sliding data
Date: Sun, 09 Oct 2011 22:25:47 +0200
Message-ID: <1318191947.2120.7.camel@localhost.localdomain> (raw)
In-Reply-To: <4E91F33A.2040908@club-internet.fr>
References: <4E917601.4040700@club-internet.fr>
<4E91D0BC.2030406@club-internet.fr>
<1318180045.2107.2.camel@localhost.localdomain>
<4E91F33A.2040908@club-internet.fr>
On Sun, 2011-10-09 at 21:17 +0200, F. BROUARD / SQLpro wrote:
> Hi,
>
> Le 09/10/2011 19:07, Guillaume Lelarge a écrit :
> > 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
> >
> >
>
> Please give a real example instead of copying the doc that I have read a
> lot !
>
> I am not so stupid to have post this topic without having try many
> syntaxes wich does not works !
>
I don't think you're stupid. You said the doc was wrong, and I answered
you it wasn't. But I understand it didn't help you solve your issue...
Anyway, if you gave us the error message, it would be easier to answer
you. Here is the error message I get:
ERROR: relation "new" does not exist
LINE 18: FROM NEW
^
And actually, you can't use "FROM NEW". And this:
INSERT INTO T_MESURE_MSR
SELECT MSR_ID,
MSR_DATE,
MSR_MESURE
FROM NEW
WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' )
has no meaning at all in PostgreSQL.
--
Guillaume
http://blog.guillaume.lelarge.infohttp://www.dalibo.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: guillaume@lelarge.info, sqlpro@club-internet.fr
Subject: Re: [GENERAL] Creating rule for sliding data
In-Reply-To: <1318191947.2120.7.camel@localhost.localdomain>
* 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