Re: Best way to restrict detail rows? - Mailing list pgsql-sql

On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <maier@med.unc.edu> wrote:
> I have a "master-detail" kind of situation, as illustrated here:
>
> CREATE TABLE master(
>        id SERIAL PRIMARY KEY,
>        foo TEXT
> );
>
> CREATE TABLE detail(
>        id SERIAL PRIMARY KEY
>        master BIGINT NOT NULL REFERENCES master(id),
>        bar TEXT
> );
>
> (this is a simplification, of course)
>
> I would like a way to restrict the addition of new detail records, but only
> after the initial detail records have been inserted into the system.

After you create the table do something like this:

create rule detail_no_insert as on insert to detail do nothing;
create rule detail_no_update as on update to detail do nothing;

poof.  no more updates or inserts work.  Note that copy will still
work, as it doesn't fire rules.  So, you can update the data with
copy, and otherwise not touch it.