Re: multiple statement 'instead of' rule

On 6/20/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > create or replace rule insert_fsv as on insert to frequency_service_view
> > do instead
> > (
> > insert into frequency_operation
> > select new.table_name, new.frequency, old.code where new.set =
> > true and old.set = false;
> > delete from frequency_operation
> > where table_name = old.table_name and frequency_operation.code =
> > old.code and
> > frequency_operation.frequency = new.frequency and new.set = false;
> > update operation
> > set code = new.code where code = old.code and old.code != new.code;
> > );
>
> What is frequency_service_view? Is it by any chance dependent on
> frequency_operation? If so, your changes to frequency_operation will
> affect the behavior of OLD references.

right, actually that was a typo, was supposed to be 'create or replace
rule insert_fov as on insert to frequency_operation_view'. I was
considering that old/new are invalid which is fine, but the problem is
in some cases the third (and sometimes second query) never fires at
all with any arguments. I confirmed this by inserting into a log
table in between the rule queries (they never fired either).

I can prepare a test case if you think it's worth it.

Merlin