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