Thread: 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