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

problem with update rules on a view (ODBC)

From: Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org>
To: pgsql-general(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org
Subject: problem with update rules on a view (ODBC)
Date: 2003-01-12 18:45:33
Message-ID: avsd4d$1i1m$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfaces
Dear list,

I am trying to create a small applications using a PostgreSQL 7.2 database, 
updated through ODBC from an OpenOffice form.

I have to work on a small (about 300 rows) subset of a larger (about 10000 
rows) ser of records, encompassing many tables. in the subset, one has to 
make updates to a boolean field, using (informally : this has to be decided 
by a human (a physician, in fact) after reading the information).

I created a view defining the subset of interest, which has two column : an 
identifier (primary key of the underlying table), and the boolean variable 
of interest. The main OpenOffice form is based on this view, and uses 
subforms to display in a convenient manner the records of the other tables 
associated with the record of the main table under examination.

I created two update rules on the view : the first one is conditional : its 
WHERE clause tests for a change of the boolean variable 
(new.value!=old.value) and, if so, updates INSTEAD the underlying table 
WHERE primarykey=new.primarykey ; the second rule, unconditional, does 
INSTEAD NOTHING. The first rule uses typecasts (OpenOffice insists to use 
'0' and '1' as a representation of booleans).

The form works OK, displays the correct set of rows and correctly 
associates subrecords with the main record. However, I cannot update 
through the form. OpenOffice detects an error during the update.

I tried my  rules "by hand" in psql : the update works as advertised in the 
docs. However, I noticed that updating the *real" table gives a message :
UPDATE 1
after updating the table, while updating the view gives the message
UPDATE 0
after "updating" the view (i. e. updating the underlying table).

The same thing happens when I create a single non-conditional INSTEAD rule 
(reporting the condition in the where clause of the update query, which is 
possible in this case) : the record gets updated, and I get an "UPDATE 0" 
message.

This might be the source of my problems : if ODBC returns something to the 
effect of "zero records updated", OpenOffice has reasons to report an error.

What do I do wrong ?

Is that a known problem ?

Is there a workaround ?

Sincerely,

					Emmanuel Charpentier

PS : Culd you please Cc: me your answers : I'm following the list (from 
time to time) through the Newsgroup interface ... E. C.

--
Emmanuel Charpentier


Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2003-01-12 19:06:00
Subject: Re: problem with update rules on a view (ODBC)
Previous:From: Bruce MomjianDate: 2003-01-12 16:28:09
Subject: Re: MOVE LAST: why?

pgsql-general by date

Next:From: Tom LaneDate: 2003-01-12 19:06:00
Subject: Re: problem with update rules on a view (ODBC)
Previous:From: karthikeyan.balasubramanianDate: 2003-01-12 10:41:40
Subject: PostgreSQL Startup Problem on Win98

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