Обсуждение: to know what columns are getting updated

Поиск
Список
Период
Сортировка

to know what columns are getting updated

От
Sajeev Mayandi
Дата:
Hi,

I have bunch of rules created for tables to implement upsert functionality.      My problem is our tables gets updated from multiple places ,  non –necessarily with the same no of columns. I want to figure out  columns are being asked to be updated 

E.g. The rule for the the table base-table is 

CREATE OR REPLACE RULE base-table-rule  AS
    ON INSERT TO base-table
   WHERE (EXISTS ( SELECT 1
           FROM base-table
          WHERE bas-table::x1 = new.x1  )) 
      DO INSTEAD  UPDATE base-table  SET x1=new.x1,x2=new.x2,x3 =new.x3,x4=new.x4
  WHERE base-table.x1= new.x1;



1) user 1 comes with the below insert
 Insert into base-table(x1,x2,x3,x4) values(v1,v2,v3,v4);

2) user 2 comes with the below insert
Insert into base-table(x1,x2) values(v1,v2);

Since user 2 uses only x1 and x2 as its column the rule replaces x3 and x4 with null. 

Is there a way to figure out that only x1 and x2 is being asked for an updating. In the above example column x1 is the primary key.

Thanks,

Sajeev

Re: to know what columns are getting updated

От
Luca Ferrari
Дата:
On Tue, Jul 30, 2013 at 6:05 AM, Sajeev Mayandi
<Sajeev_Mayandi@symantec.com> wrote:
> Hi,
>
> I have bunch of rules created for tables to implement upsert functionality.
> My problem is our tables gets updated from multiple places ,  non
> –necessarily with the same no of columns. I want to figure out  columns are
> being asked to be updated
>
> E.g. The rule for the the table base-table is
>
> CREATE OR REPLACE RULE base-table-rule  AS
>     ON INSERT TO base-table
>    WHERE (EXISTS ( SELECT 1
>            FROM base-table
>           WHERE bas-table::x1 = new.x1  ))
>       DO INSTEAD  UPDATE base-table  SET x1=new.x1,x2=new.x2,x3
> =new.x3,x4=new.x4
>   WHERE base-table.x1= new.x1;
>

I suppose this is for loggin purposes, so I would suggest to DO ALSO
and add a debuggin statement, like for instance a log entry in a table
or a raise instruction. Could it solve the problem?
Anyway it seems to me there's a design problem: essentially you are
converting an insert on duplicated key into an update, would not be
better to use the right statement for the right purpose?

Luca