From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | stan <stanb(at)panix(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Detecting which columns a query will modify in a function called by a trigger |
Date: | 2020-03-03 18:48:29 |
Message-ID: | 7ac6f67e-f330-1825-02e3-fa5b5a1a2940@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-general |
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 <stanb(at)panix(dot)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
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Hellmuth Vargas | 2020-03-03 19:45:33 | Re: Detecting which columns a query will modify in a function called by a trigger |
Previous Message | stan | 2020-03-03 17:42:48 | Re: Detecting which columns a query will modify in a function called by a trigger |
From | Date | Subject | |
---|---|---|---|
Next Message | Hernan Jesus Gonzalez Carmona | 2020-03-03 19:21:21 | Exportacion por lotes |
Previous Message | Tom Lane | 2020-03-03 17:59:00 | Re: Examing cotets of NEW & OLD in a function programed in perl |