Skip site navigation (1) Skip section navigation (2)

RE: Updating a view

From: "Ryan C(dot) Bonham" <Ryan(at)srfarms(dot)com>
To: Pete Leonard <pete(at)hero(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: RE: Updating a view
Date: 2001-08-15 19:37:36
Message-ID: 19AB8F9FA07FB0409732402B4817D75A038A6B@FILESERVER.SRF.srfarms.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc
Ok, I understand that, i think. :)  Why i was wiating for a resonce i
decided to link the VB form to a select statment on a table.. I get the same
error message.. Ex.

Given 

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

Select * from name = 'Joe'

If i try to switch records after making chages to comment i get the error
(-2147217842).. Only other thing that might effect this is that i have other
tables/views open in subforms that are related to this table.. Do i possible
need to close thoose connections first..(I don't see why i would, as they
should requery when i move the the next record anyways)...  

Thanks for the help.


> -----Original Message-----
> From: Pete Leonard [mailto:pete(at)hero(dot)com]
> Sent: Wednesday, August 15, 2001 12:22 PM
> To: Ryan C. Bonham
> Cc: pgsql-odbc(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> > 
> 
> 

Responses

pgsql-odbc by date

Next:From: Cedar CoxDate: 2001-08-15 21:10:59
Subject: Re: Changing ODBC users with Access doesn't work correctly for me
Previous:From: Dwayne MillerDate: 2001-08-15 18:50:41
Subject: Re: Re: To be 7.1.3 or not to be 7.1.3?

pgsql-general by date

Next:From: chris markiewiczDate: 2001-08-15 20:16:01
Subject: RE: hangs while getting large objects...
Previous:From: Dave CramerDate: 2001-08-15 19:26:12
Subject: RE: [JDBC] hangs while getting large objects...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group