How to return a record from function executed by INSERT/UPDATE rule?
- by seas
Do the following scheme for my database:
create sequence data_sequence;
create table data_table
{
id integer primary key;
field varchar(100);
};
create view data_view as
select id, field from data_table;
create function data_insert(_new data_view) returns data_view as
$$declare
_id integer;
_result data_view%rowtype;
begin
_id := nextval('data_sequence');
insert into data_table(id, field) values(_id, _new.field);
select * into _result from data_view where id = _id;
return _result;
end;
$$
language plpgsql;
create rule insert as on insert to data_view do instead
select data_insert(new);
Then type in psql:
insert into data_view(field) values('abc');
Would like to see something like:
id | field
----+---------
1 | abc
Instead see:
data_insert
-------------
(1, "abc")
Is it possible to fix this somehow?
Thanks for any ideas.
Ultimate idea is to use this in other functions, so that I could obtain id of just inserted record without selecting for it from scratch. Something like:
insert into data_view(field) values('abc') returning id into my_variable
would be nice but doesn't work with error:
ERROR: cannot perform INSERT RETURNING on relation "data_view"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
I don't really understand that HINT. I use PostgreSQL 8.4.
