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