to know what columns are getting updated
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
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 updatedE.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general