problem with update rules on a view (ODBC)

Поиск
Список
Период
Сортировка
Искать

problem with update rules on a view (ODBC)

От:
Emmanuel Charpentier <charpent@bacbuc.dyndns.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

Re: problem with update rules on a view (ODBC)

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Emmanuel Charpentier  writes:
> I am trying to create a small applications using a PostgreSQL 7.2 database, 
> updated through ODBC from an OpenOffice form.
> ...
> 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).

You can control this in 7.3 by adjusting the rule firing order (which
you do by choosing the names you give to the rules); see
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html

I am not sure there is any good workaround in 7.2.

			regards, tom lane
FAQ