Re: multiple statement 'instead of' rule

On 6/20/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.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