Re: Updating a view

Ryan,

Have you created a rule for updating the view?

In short - because views are derived from an underlying set of tables, the
database cannot safely insert/update/delete through a view until rules
defining exactly what to do are created.

Given:

table foo (
a    integer NOT NULL,
name    varchar(50) NOT NULL
);

table bar (
b    integer NOT NULL,
nameb    varchar(50) NOT NULL
);

create view foobar as
select f.name, b.nameb, f.a from foo f, bar b where f.a = b.b;

the rule to handle an update would be something like:

create rule update_foobar as on update to foobar
do instead (
update foo set name=NEW.name where a = NEW.a;
update bar set nameb=NEW.nameb where b = NEW.b;
);


Check out the postgres documentation for rules & views - it'll cover all
of this.

    --pete



On Wed, 15 Aug 2001, Ryan C. Bonham wrote:

> Hi,
>
> Ok i know this is something stupid, somebody mind explaining it to me? Also
> sorry if this gets posted twice, i got a error back the first time..
>
> I have a Visual Basic Project that access a PostgreSQL Datbase. One Form
> pulls information from a View in postgres. If teh user changes anything on
> the form the update fails. I tried running hte dame update from PGAdmin and
> it also fails with the following message. I just dont understand what i need
> to create i guess..  Thanks for the help..
>
> Ryan
>
> *******************************************************
> * pgAdmin v7.1.0: Error - 2001-07-19 11:31:42
> *******************************************************
>
> Error Details
> *************
> Error Number: -2147217842
> Error Description: Operation was canceled.
> Error Source: Microsoft OLE DB Provider for ODBC Drivers
> Subroutine of Function: frmODBCLogon, cmdOK_Click
>
> System Details
> **************
> Operating System: Windows NT v5.0 Build 2195
> Additional Info: Service Pack 2
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>