Solved (thank you postgresql!) was Re: how to switch a view easily?

[prev] [thread] [next] [Date index for 2005/04/20]

Yuppers.  That did it just fine.

In my Booking.pm, I have:

    sub exclude_cancelled {
      my $self = shift;
      $self->table('livebooking');
    }

    sub include_cancelled {
      my $self = shift;
      $self->table('booking');
    }

    __PACKAGE__->exclude_cancelled(); # the default

And I've defined the view as:

    CREATE OR REPLACE VIEW livebooking AS
    SELECT * FROM booking WHERE cancelled_date IS NULL;

    CREATE OR REPLACE RULE "delete" AS
    ON DELETE TO livebooking
    DO INSTEAD DELETE FROM booking WHERE (booking.booking_id = OLD.booking_id);

    -- we can cheat here because we know booking.* is livebooking.* order
    CREATE OR REPLACE RULE "insert" AS
    ON INSERT TO livebooking
    DO INSTEAD INSERT INTO booking VALUES (NEW.*);

    -- but the next one has no cheat :-(
    CREATE OR REPLACE RULE "update" AS
    ON UPDATE TO livebooking
    DO INSTEAD UPDATE booking SET
      -- list every single friggin column, ugh
      foo = NEW.foo,
      bar = NEW.bar,
      -- and so on ...
      cancelled_date = NEW.cancelled_date
    WHERE booking.booking_id = OLD.booking_id;

Thank you, postgresql!

When people say "I don't need views.  I don't need rules.  I don't
need triggers.", they just haven't worked SQL long enough.  Going
back in to create all the alternate SQL queries would have been
maddening!

        -- 
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@xxxxxxxxxx.xxx> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!