From: Emmanuel Charpentier <charpent@bacbuc.dyndns.org>
Subject: problem with update rules on a view (ODBC)
Date: Sun, 12 Jan 2003 19:45:33 +0100
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 58
Message-ID: <avsd4d$1i1m$1@news.hub.org>
Reply-To: charpent@bacbuc.dyndns.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.2.1) Gecko/20021226 Debian/1.2.1-9
To: pgsql-general@postgresql.org, pgsql-interfaces@postgresql.org

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