Date: Tue, 3 Mar 2020 12:42:48 -0500
From: stan <stanb@panix.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Detecting which columns a query will modify in a function called
 by a trigger
Message-ID: <20200303174248.GB5019@panix.com>
References: <20200302185904.GA6528@panix.com>
 <30c3c06e-61fa-3210-666d-1696af320a75@aklaver.com>
 <20200302202829.GA19161@panix.com>
 <CAKFQuwaYuvQert-Szy7khu4tB6avCah0wqavsQzPDxX+-vjj_g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKFQuwaYuvQert-Szy7khu4tB6avCah0wqavsQzPDxX+-vjj_g@mail.gmail.com>
Precedence: bulk

On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@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?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin