problem with update rules on a view (ODBC) - Mailing list pgsql-interfaces

From Emmanuel Charpentier
Subject problem with update rules on a view (ODBC)
Date
Msg-id avsd4d$1i1m$1@news.hub.org
Whole thread Raw
Responses Re: [GENERAL] problem with update rules on a view (ODBC)
List pgsql-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


pgsql-interfaces by date:

Previous
From: "Key88 SF"
Date:
Subject: Re: libpqxx Large Objects
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] problem with update rules on a view (ODBC)