Combining insert rules on views with functions

From: Bastiaan Olij <bastiaan(at)basenlily(dot)me>
To: pgsql Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Combining insert rules on views with functions
Date: 2015-09-17 03:21:46
Message-ID: 55FA31CA.7040402@basenlily.me
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2015-09-17 03:46:46 Re: Combining insert rules on views with functions
Previous Message Tom Lane 2015-09-11 18:12:22 Re: PostgreSQL source code: copy command