From: Bradley Kieser <brad@kieser.net>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: Rules on a view overwrite default values. Any way to
Date: Mon, 03 Jan 2005 17:17:01 +0000
Message-ID: <41D97E0D.1030202@kieser.net> (raw)
In-Reply-To: <1104772155.23251.10.camel@state.g2switchworks.com>
References: <41D97A9C.6050401@kieser.net>
<1104772155.23251.10.camel@state.g2switchworks.com>
Hey Scott, that's ingenious, only thing is that I use NOT NULL for many
data columns too, where the value may or may not be passed in. Will try
with a COALESCE and will post back here.
Thx again!
Brad
Scott Marlowe wrote:
>On Mon, 2005-01-03 at 11:02, Bradley Kieser wrote:
>
>
>>Hi,
>>
>>I use views extensively to implement security on tables. This requires
>>each table to have a view. All updates, inserts and deletes take place
>>through the view. The view has rules for each of these operations
>>defining security for that table. Under other conditions, the view also
>>removes complex views of the underlying data from the application layer
>>by supplying a view where the rules for update, insert and delete
>>implement business logic.
>>
>>The problem is that rules on a view mean that the default values for NOT
>>NULL columns (used extensively) no longer trigger!
>>
>>E.g.
>>
>>Table A had column "updated_time" which is not null default now().
>>Inserting into the view on table A where "updated_time" has not been
>>supplied will not fill in "now()". It will attempt to put in a null
>>value and hence the insert will fail in the insert rule on that view.
>>
>>Is there any way to tell PG to implement the triggers on the underlying
>>table? It will make it extremely difficult to implement this schema if I
>>have to try to put in null field handling... and it really should be PG
>>doing this not me! No doubt if this is a bug, it will be fixed in 8.x!
>>
>>
>
>Try changing the udpate triggers you're creating to pass in DEFAULT
>(similar to NULL in the way it's NOT quoted, etc...) in the update /
>insert query to the lower level and see if that fixes things.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: brad@kieser.net, smarlowe@g2switchworks.com
Subject: Re: Rules on a view overwrite default values. Any way to
In-Reply-To: <41D97E0D.1030202@kieser.net>
References: <41D97A9C.6050401@kieser.net> <1104772155.23251.10.camel@state.g2switchworks.com> <41D97E0D.1030202@kieser.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox