PLPGSQL : Return a record from function executed by INSERT/UPDATE rule

Posted by seas on Stack Overflow See other posts from Stack Overflow or by seas
Published on 2010-04-11T01:57:37Z Indexed on 2010/04/11 2:03 UTC
Read the original article Hit count: 481

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.

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about plpgsql