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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL source code: copy command
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Combining insert rules on views with functions