Hi postgres experts, I have encountered what is, to me, a slightly tricky problem when using rules and I am in need of some help.
I am using rules to simulate an old version of a database schema for backward-compatibility while migrating to a modified schema. For instance, there used to be a table a which is now replaced by table b that contains much the same data. To enable old code to still work, I create a view called a that simulates the appearance of the old table, and do instead rules to redirect all operations on it to the actual table, b. The problem is that when inserting to the view, default values do not work as expected. For instance, with a table definition and a rule like the following: CREATE TABLE b ( foo INTEGER, bar INTEGER DEFAULT 5 ); CREATE RULE insert AS ON INSERT TO a DO INSTEAD INSERT INTO b (foo,bar) VALUES (NEW.foo,NEW.bar); executing the following query: INSERT INTO a(foo) VALUES (1); results in bar being NULL instead of 5. A partial solution is to write the rule as follows: CREATE RULE insert AS ON INSERT TO a DO INSTEAD INSERT INTO b (foo,bar) VALUES (NEW.foo,COALESCE(NEW.bar,5)); effectively implementing the default value in the rule. However, this fails in the case that you explicitly insert NULL. For example: INSERT INTO a(bar) VALUES (NULL); results in bar having the value 5 instead of being NULL. Interestingly, update rules treat columns that appear in the rule but are omitted from a query as expected, i.e. such columns are not affected by the query. Is there a way of getting the behaviour that I want, or is this not possible using rules? Am I perhaps approaching this in completely the wrong way? Would there be a better way of implementing backward-compatibility with the old table definition? On a related note, I have noticed that you cannot create triggers on updateable views. Is there a reason why this could never be possible or is it not something you should want to do (e.g. because it is not possible to define clearly what this would mean) or is this essentially an arbitrary limitation that might change in the future? I am using version 8.1. Thanks for any help. -- Mario Becroft (postgres-related mail) <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org