Help on Update Rule for View

From: "Lenorovitz, Joel" <Joel(dot)Lenorovitz(at)usap(dot)gov>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Help on Update Rule for View
Date: 2006-12-05 19:03:16
Message-ID: 7119BB016BDF6445B20A4B9F14F50B2D44A8C7@WILSON.usap.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2006-12-05 19:26:50 Re: HELP: Urgent, Vacuum problem
Previous Message Tomi N/A 2006-12-05 18:38:08 Re: Speed of postgres compared to ms sql, is this