Howdy,
I'm trying to create a schema in which there will be simple a view for
each table that will have the same columns and can be acted on in the
same way as the underlying table
An example of one table and its view would be:
CREATE TABLE test (id int, text_field varchar(100));
CREATE VIEW _test AS SELECT * FROM test;
I'd like to be able to create both the views and the insert, update,
delete rules for the views in an automated fashion via a script that
uses the information schema to get all of the table names. All is fine
and good with the insert and delete rules and no problem to
automatically generate this:
CREATE RULE _test_oi_rule AS ON INSERT TO _test DO INSTEAD INSERT INTO
test VALUES (NEW.*);
CREATE RULE _test_od_rule AS ON DELETE TO _test DO INSTEAD DELETE FROM
test WHERE id = OLD.id;
However, I'm not sure how to create the update rule without having to go
through the gory task of specifying each column by name. Yes, I could
also use the information schema to automate this as well, but it just
seems ugly. Is there any way to create an update rule that's something
like this:
CREATE RULE _test_ou_rule AS ON UPDATE TO _test SET test.* = NEW.*;
-- or even better a command that will only update changed columns (i.e.,
WHERE NEW.* <> OLD.*)
I imagine I could instead delete the old record and insert the new one,
but that doesn't seem right either and seems like could be perilous.
Maybe I'm overlooking something obvious, but any help to find a nice
clean solution would be appreciated.
Thanks,
JL