Preserving postgres default values on tables that have views and update rules

The Postgres database has many strengths, one of the most powerful being the rules and triggers system. Combined with views, rules and triggers allow you to control access to data in underlying tables, stricting users to seeing only the data that they are allowed to see and to enforce business logic. Even complex views with data that comes from many tables through complex joins can be made updatable (insert, update and delete) through using update rules. This can dramatically simplify and speed up application development and makes
rapid application development (for example using Borland's Jbuilder).

To enable views to be used for updating the underlying datasets, you have to create update rules.This implies creating one or more rule for each update action: Update, delete and insert.

The postgres manual for rule creation is here (http://www.postgresql.org/docs/manuals).

Here is an example of creating a table, a view and an updatable rule, in this case for inserts:

create table test1 (id serial, col1 integer not null default 10, col2 text not null);

create view test1v as select * from test1;

create rule testins as on insert to test1v do instead (

insert into test1 (col1,col2) values (NEW.col1,NEW.col2);

);

However, the default values for col1 and the id columns in test1 will not be preserved on insert into view est1v. Inserting a null value into these columns will cause a not-null violation. Postgres does not propogate the rules and triggers in the object beneath the view into the update rules on the view. To do this you need to explicitly add these constraints to the view using alter table.

In this case we have the col1 default value constraint to apply:

alter table test1v alter column col1 set default 10;

We now have the default values added to the view. In this way you can build up very complex views, abstracting a good underlying database design, adding strong security and maintaining the sort of database interface that RAD tools such as JBuilder and Delphi excel at using.

Honeypot: spam@kieser.net