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:
Show quoted text
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_ClickSystem 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
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.
Show quoted text
-----Original Message-----
From: Pete Leonard [mailto:pete@hero.com]
Sent: Wednesday, August 15, 2001 12:22 PM
To: Ryan C. Bonham
Cc: pgsql-odbc@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Updating a viewRyan,
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_ClickSystem 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
Import Notes
Resolved by subject fallback
Hi,
I'm a little new to postgres, and I'm having trouble droping a sequence.
I get the following error:
ERROR: mdopen: couldn't open scotty_scottyid_seq: No such file or
directory
when I try to recreate the sequence I get the following:
gsheet=> create sequence scotty_scottyid_seq;
ERROR: Relation 'scotty_scottyid_seq' already exists
Thanks in advance,
Killian May
InfoSport Inc
killian.may@infosport.com
(902)423-0662 fax(902)423-0390
Hi,
Thanks to everyone who answered my questions.. I got the problem solved.
Thanks
Ryan
Show quoted text
-----Original Message-----
From: Fernando Schapachnik [mailto:fschapachnik@vianetworks.com.ar]
Sent: Wednesday, August 15, 2001 12:28 PM
To: Ryan C. Bonham
Cc: pgsql-odbc@postgresql.org; pgsql-general@postgresql.org
Subject: [ODBC] Re: [GENERAL] Updating a viewI'm not sure about Postgres in particular, but updating a view is not
always possible. ConsiderCREATE 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---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Import Notes
Resolved by subject fallback
On Mon, 20 Aug 2001 18:19:49 +0000 (UTC), Killian May <kmay@infosport.com>:
Hi,
I'm a little new to postgres, and I'm having trouble droping a sequence.
I get the following error:ERROR: mdopen: couldn't open scotty_scottyid_seq: No such file or
directorywhen I try to recreate the sequence I get the following:
gsheet=> create sequence scotty_scottyid_seq;
ERROR: Relation 'scotty_scottyid_seq' already exists
Which version are you running?
select version();
should give a precise answer.
Show the command you are using to drop the sequence
like you do here for creating the sequence.
Can you get values from the sequence?
select nextval('scotty_scottyid_seq')
and show the output.