Обсуждение: Combining insert rules on views with functions
Hi, I've got a situation where I'm adding an insert rule to a view but the work I need to do in order to insert the required data doesn't fit well in a simple query rewrite (I need to calculate a few things before I'm ready to do my insert). Because of this I've moved the actual insert code into a function which in looks sort of like this: ---- create function myInsert(bitOfData1, bitOfData2) returns myView as $BODY$ DECLARE lvPKey integer; DECLARE lvValue1 datatype; DECLARE lvValue2 datatype; DECLARE lvValue3 datatype; DECLARE lvNewRow myView; BEGIN -- do some calculations here lvValue1 := <some nice funky calculation here>; ... -- insert data insert into myTable (col1, col2, col3) values (lvValue1, lvValue2, lvValue3) returning primaryKeyCol into lvPKey; -- return data select * into lvNewRow from myView where primaryKeyCol = lvPKey; END $BODY$ ---- And now I simply call my function from my insert rule. As this is however now a call to a function and not an insert query I'm having some trouble figuring out if I can properly implement a returning clause so I could do a: insert into myView (col1, col2) value ('data1', 'data'2) returning primaryKeycol into .... ; I've currently setup my rule as this: ---- create rule myView_insert as on insert to myView do instead select * from myInsert(NEW.col1, NEW.col2); ---- which I'm pretty sure is not the right way to do this. Right now the insert query gets rewritten to a select query and I get my new row as a normal result set. That in itself is workable but does not conform to the proper way an insert query works. Am I trying to do something that simply goes to far or is there a way to do this properly? Cheers, Bas
Bastiaan Olij <bastiaan@basenlily.me> writes: > I've got a situation where I'm adding an insert rule to a view but the > work I need to do in order to insert the required data doesn't fit well > in a simple query rewrite (I need to calculate a few things before I'm > ready to do my insert). > Because of this I've moved the actual insert code into a function which > in looks sort of like this: ... umm, have you considered using a BEFORE INSERT trigger instead? AFAICT, what you really want here is to compute some derived column values before the row gets stored, and a trigger would handle that nicely without any need to abuse semantics. regards, tom lane
Bastiaan Olij <bastiaan@basenlily.me> writes: > Would a before insert trigger work on a view as well? Ah, sorry, wasn't paying close-enough attention. For a view, you can only provide an INSTEAD OF trigger, and that feature only exists in fairly recent PG releases. But if you're running such a release then I recommend looking into that way. Rules are, um, not the best part of Postgres. regards, tom lane
Hi Tom, It does seem they have a few short comings but at the same time they hold a lot of promise as well. I also realised that using a before trigger on the view wouldn't work because the view doesn't contain all the columns in the underlying table that will end up being set. The best course of action seems to be to use a simple rewrite rule on the trigger to just insert the data as is on the underlying table and put the before trigger on the underlying trigger however I have two things to solve: 1) my before trigger needs to work on a subset of my table, but I think I'll be able to handle using the triggers condition 2) my code is handling a bunch of BI that currently happens client side in legacy products talking to this database that I'm trying to move server side. For some time the legacy code will live side by side with the new implementation through the views and that introduces the risk of doubling up on logic. Adding everything to the view was a nice way to segregate the two approaches, I'll have to think of something new:) Just thinking ahead as after solving the insert I'll be doing something similar for updates on the view. Is there a way to detect which columns are added to the "SET" part of my query (even if the value remains unchanged) and will that survive the rewrite in the rule? I.e. if my view has 15 columns but I do an; update myView set col2 = 123, col5 = 567 where primarykey = 891; Will I be able to know in my before update trigger on the table I'm updating that those are the only columns listed, which I guess is extra tricky because the column name of the view will not match that of the update? Cheers, Bas On 17/09/2015 2:14 pm, Tom Lane wrote: > Bastiaan Olij <bastiaan@basenlily.me> writes: >> Would a before insert trigger work on a view as well? > Ah, sorry, wasn't paying close-enough attention. > > For a view, you can only provide an INSTEAD OF trigger, and that feature > only exists in fairly recent PG releases. But if you're running such a > release then I recommend looking into that way. Rules are, um, not the > best part of Postgres. > > regards, tom lane > > -- Kindest Regards, Bastiaan Olij e-mail: bastiaan@basenlily.me web: http://www.basenlily.me Skype: Mux213 http://www.linkedin.com/in/bastiaanolij