Postgres Pro
Downloads
Home   >   mailing lists

Thread: Updating a view

Updating a view

From
"Ryan C. Bonham"
Date:
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

Re: Updating a view

From
Fernando Schapachnik
Date:
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

Re: Updating a view

From
Pete Leonard
Date:
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
>

RE: Updating a view

From
"Ryan C. Bonham"
Date:
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@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 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
> >
>
>

Missing Sequence File

From
Killian May
Date:
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

Re: Missing Sequence File

From
missive@frontiernet.net (Lee Harr)
Date:
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
>  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
>

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.