On 3/3/20 1:32 PM, stan wrote:
Please reply to list also.
Ccing list
On Tue, Mar 03, 2020 at 10:48:29AM -0800, Adrian Klaver wrote:
On 3/3/20 9:42 AM, stan wrote:
On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
On Mon, Mar 2, 2020 at 1:28 PM stan <st...@panix.com> wrote:
Envision a table with a good many columns. This table represents the "life
history" of a part on a project. Some of the columns need to be
created/modified by the engineer. Some need to be created/modified by the
purchasing agent, some of the columns need to be created by the receiving
department, some of the columns need to be created/modified by the accounts
payable department.
Make sense?
On a theory level this design is insufficiently normalized. The fact that
you are having issues and challenges working with it suggests you should
seriously consider a different design, one that exhibits better
normalization properties.
Alternatively you might consider just removing direct access to the table
and provide views and/or functions that can use normal permission grants.
Add some check constraints to the table to describe and enforce the
inter-field relationships that are present.
Thanks for the input.
I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.
Am I suffering from a lack of knowledge here?
Yes:
https://www.postgresql.org/docs/12/sql-createview.html
Updatable Views
OK, looking at that makes me think it is fairly limited. EG an Updatable
view can have on one FROM clause. Thus it by definition cannot use a JOIN.
So if I have a table with may columns, I can create a view that is a subset
of the available columns, which might be a way to address my issue.
The link was for automatically updateable views. If you want to do
something more involved then see:
https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
Thanks.
--
Adrian Klaver
adrian.kla...@aklaver.com
