Re: Dont allow updation for few columns in a record. - Mailing list pgsql-novice

Has anyone considered implementing per-column updating?  Oracle has
this.
Given a table with columns a, b and c, you can do "grant update on
table(a,b) to user".

Jeff
On Friday, July 11, 2003, at 05:35  PM, Dmitry Tkach wrote:

> HK wrote:
>
>> hi all,
>> thanx bruno. If we dont grant update access to the table, i will not
>> be able to do any updations.
>> I only want to prevent a particular column from being updated.
>> Is there any way??
>> TIA.
>>
> Split your table into two - one, containing the columns you wnat to
> update, and the other one 'read-only', and make them related via a
> foreign key:
>
> create table prod_def
> (
>    id int primary key,
>    name text
> );
> revoke update on prod_def from public;
> create table prod_desc
> (
>    id int primary key references prod_def,
>    description text
> );
> grant update on prod_def to public;
>
> You can also set up a view, and a couple of rules, to make it look
> like your original single table:
>
> create view product as select def.id as prod_id, name as prod_name,
> description as prod_desc from prod_def def natural join prod_desc;
> create rule new_product as on insert to product do instead
> (
>   insert into prod_def values (new.prod_id, new.prod_name);
>   insert into prod_desc values (new.prod_id, new.prod_desc);
> );
>
> create rule update_product as on update to product do instead
> (
>   update prod_desc set description = new.prod_desc, id = new.prod_id
> where id = old.prod_id;
> );
>
> This will simply ignore attempts to change the product's name... If
> you want such attempts to cause an error, you can
> add this to the action:
>
> update prod_def set name = new.prod_name where new.prod_name <>
> old.prod_name and id = new.prod_id;
>
> This will cause an exception if the user is trying to change the
> product's name, and is not allowed to do that.
>
> I hope, it helps...
>
> Dima
>
>
>
>>
>> On Wed, 9 Jul 2003, Bruno Wolff III wrote:
>>
>>
>>> On Wed, Jul 09, 2003 at 11:40:24 +0530,
>>>  HK <harikrishnan@midascomm.com> wrote:
>>>
>>>> Hi all,
>>>>     Is it possible to say that once a record is inserted into the
>>>> table, nobody can update some particular columns in that record.
>>>> (eg)
>>>> table
>>>> ------
>>>> prod_id prod_name prod_desc
>>>>  1      name1      desc1
>>>>  2      name2      desc2
>>>>
>>>> In this table i dont want to allow updation of the prod_name column.
>>>>
>>>> I can write a trigger to do this job, but is there any way to
>>>> specify during the table creation time itself that these columns
>>>> cannot be altered.
>>>>
>>> One option would be not to grant UPDATE access to the table.
>>>
>>>
>>
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings