Search Postgresql Archives

Re: Detecting which columns a query will modify in a function called by a trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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@xxxxxxxxxxx) 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@xxxxxxxxx> 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@xxxxxxxxxxx




--
Cordialmente,

Ing. Hellmuth I. Vargas S.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux