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
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general 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

Responses

Browse pgsql-interfaces by date

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

Browse pgsql-general by date

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