From: Hellmuth Vargas <hivs77@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: stan <stanb@panix.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Detecting which columns a query will modify in a function called by a trigger
Date: Tue, 3 Mar 2020 14:45:33 -0500
Message-ID: <CAN3Qy4oB7CKTg-+J9583VcU7_YHkv2xG3BNvf_Mu_qAdjPyYsw@mail.gmail.com> (raw)
In-Reply-To: <7ac6f67e-f330-1825-02e3-fa5b5a1a2940@aklaver.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>
hello
By means of json you can detect what change between NEW and OLD
example:
select b.*
from
(values (now(),123456,'pepito perez',false)) as
old(dato1,dato2,dato3,dato4),json_each_text(row_to_json(old)) as
b(text1,text2)
except
select b.*
from
(values (now(),98765,'pepito perez',true)) as
new(dato1,dato2,dato3,dato4),json_each_text(row_to_json(new)) as
b(text1,text2)
El mar., 3 de mar. de 2020 a la(s) 13:48, Adrian Klaver (
adrian.klaver@aklaver.com) escribió:
> 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
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
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: hivs77@gmail.com, adrian.klaver@aklaver.com, stanb@panix.com, david.g.johnston@gmail.com, pgsql-general@lists.postgresql.org
Subject: Re: Detecting which columns a query will modify in a function called by a trigger
In-Reply-To: <CAN3Qy4oB7CKTg-+J9583VcU7_YHkv2xG3BNvf_Mu_qAdjPyYsw@mail.gmail.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