Обсуждение: Updating a view

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

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
>

I'm not sure about Postgres in particular, but updating a view is not
always possible. Consider

CREATE VIEW TEST AS
SELECT sum(money) FROM table;

What would it mean to update an aggregate?

The propper and abstract way to do that in general is to define an
stored procedure (they would be kind of the setters of the object)
and a view (kind of the getters).

Regards.


En un mensaje anterior, Ryan C. Bonham escribió:
> 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..


Fernando P. Schapachnik
Planificación de red y tecnología
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381