Re: Updates on Views?

On Thu, Mar 22, 2001 at 09:48:43PM +0100, Konstantinos Agouros wrote:
> In <Pine.BSF.4.21.0103212025030.38659-100000@megazone23.bigpanda.com> sszabo@megazone23.bigpanda.com (Stephan Szabo)
writes:
> >On 21 Mar 2001, Konstantinos Agouros wrote:
> >> are views in PG read-only or is this possible?

views are read-only -- but interestingly, a view is actually an
empty table with interference-running rules attached. you can
overlay your own 'do instead' rules to effect various
behind-the-scenes inserts and updates...

> >You can make updatable views by making the appropriate rules
> >on the view for handling insert/update/delete (whatever
> >you want).
> Could You give me a hint on where to read up on this?

on my debian system it's mentioned in the postgresql-doc package
at...

    % grep -rl INSTEAD /usr/share/doc/postgresql-doc/html
    /usr/share/doc/postgresql-doc/html/programmer/rules1139.htm
    /usr/share/doc/postgresql-doc/html/programmer/rules978.htm
    /usr/share/doc/postgresql-doc/html/user/sql-createrule.htm
    /usr/share/doc/postgresql-doc/html/user/syntax.htm

i'd look in USER documentation under CREATE RULE to get started.
(also at postgresql.org user-lounge area)

--

okay, here's a quickie example:

    CREATE VIEW who AS
        SELECT * from _who;

    CREATE RULE
        who_insert
    AS ON
        INSERT TO who
    DO INSTEAD
        INSERT INTO "_who" (
            login,
            "password",
            hint,
            name,
            email,
            editor,
            status,
            modified,
            created,
            id
        ) VALUES (
            NEW.login,
            NEW."password",
            NEW.hint,
            NEW.name,
            NEW.email,
            NEW.editor,
            'U'::bpchar, -- uncertain until confirmed
            "timestamp"('now'::text), -- last mod
            "timestamp"('now'::text), -- created now
            nextval('_who_id_seq'::text)
    );
    -- all non-mentioned fields from _who are silently
    -- ignored (and dropped).

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!