Skip site navigation (1) Skip section navigation (2)

Re: Best way to restrict detail rows?

From: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Best way to restrict detail rows?
Date: 2008-12-08 21:31:43
Message-ID: 396486430812081331p3ec67892yade5f580c3d2f451@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Mon, Dec 8, 2008 at 1:28 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <maier(at)med(dot)unc(dot)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.


One Idea that popped into my head that may-or-may-not work would be to
add a constraint trigger that checks if all of the detail records have
the same xmin as the order table record.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

In response to

Responses

pgsql-sql by date

Next:From: Scott MarloweDate: 2008-12-08 21:34:54
Subject: Re: Best way to restrict detail rows?
Previous:From: Scott MarloweDate: 2008-12-08 21:29:49
Subject: Re: Best way to restrict detail rows?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group