From: Adrian Klaver <adrian.klaver@aklaver.com>
To: stan <stanb@panix.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Detecting which columns a query will modify in a function called by a trigger
Date: Tue, 3 Mar 2020 14:48:41 -0800
Message-ID: <5574e3a6-2245-42c4-77fd-6e51a7a65e0c@aklaver.com> (raw)
In-Reply-To: <20200303213215.GA2023@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>
<20200303174248.GB5019@panix.com>
<7ac6f67e-f330-1825-02e3-fa5b5a1a2940@aklaver.com>
<20200303213215.GA2023@panix.com>
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 <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?
>>
>> 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.klaver@aklaver.com
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 using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: adrian.klaver@aklaver.com, stanb@panix.com, pgsql-general@postgresql.org
Subject: Re: Detecting which columns a query will modify in a function called by a trigger
In-Reply-To: <5574e3a6-2245-42c4-77fd-6e51a7a65e0c@aklaver.com>
* 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