Combining insert rules on views with functions
От | Bastiaan Olij |
---|---|
Тема | Combining insert rules on views with functions |
Дата | |
Msg-id | 55FA31CA.7040402@basenlily.me обсуждение исходный текст |
Ответы |
Re: Combining insert rules on views with functions
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: