[SQL] Help with inserts into Views

[Brian Powell]

Greetings,

I am having difficulty creating a way to insert into a view which joins
multiple tables (specifically a view which joins multiple records from a
single table to a record in another table).

Please see the enclosed simple example for how I am currently inserting
(updating, etc.) on views.  However, When I get more complex tables with
multiple joins, this method will not work because a plpgsql function can
accept only 16 arguments.  I have tried using the NEW variable as the
argument to the function; however, this does not seem to work.

No matter what I do to create an insert trigger on the view, it never seems
to fire the trigger.

Any help and suggestions on how to perform an insert into multiple tables
from a single joined view would be greatly appreciated.

Thank you,
Brian

Example:

drop sequence addr_id_seq;
drop sequence member_id_seq;

drop rule v_member_insert;
drop function member_insert(varchar, varchar, varchar, varchar, varchar,
varchar, varchar);
drop view v_member;
drop table member;
drop table addr;


create sequence addr_id_seq;
create table addr (
   addr_id    int4 primary key default nextval('addr_id_seq'),
   street     varchar(40) not null,
   city       varchar(40) not null,
   state      varchar(40) not null
);

create sequence member_id_seq;
create table member (
   member_id    int4 primary key default nextval('addr_id_seq'),
   username     varchar(40) not null,
   address_id   int4 not null,
   shipping_id  int4 not null
);


create view v_member as
  select m.member_id, m.username, a.street, a.city, a.state,
         s.street as ship_street, s.city as ship_city,
         s.state as ship_state
  from member m, addr a, addr s
  where m.address_id = a.addr_id and m.shipping_id = s.addr_id;


create function member_insert(varchar, varchar, varchar, varchar,
varchar, varchar, varchar) returns text as '
declare
  my_address_id integer;
  my_shipping_id integer;

  my_username ALIAS FOR $1;
  my_street ALIAS FOR $2;
  my_city ALIAS FOR $3;
  my_state ALIAS FOR $4;
  my_ship_street ALIAS FOR $5;
  my_ship_city ALIAS FOR $6;
  my_ship_state ALIAS FOR $7;

begin
  my_address_id := nextval(''addr_id_seq'');
  insert into addr
    (addr_id, street, city, state)
    values (my_address_id, my_street, my_city, my_state);

  my_shipping_id := nextval(''addr_id_seq'');
  insert into addr
    (addr_id, street, city, state)
    values (my_shipping_id, my_ship_street, my_ship_city,
my_ship_state);

  insert into member (username, address_id, shipping_id)
    values (my_username, my_address_id, my_shipping_id);

   return ''Success'';
end;
' language 'plpgsql';


CREATE RULE v_member_insert AS
ON INSERT TO v_member
DO INSTEAD
  
  SELECT member_insert(new.username, new.street, new.city, new.state,
new.ship_street, new.ship_city, new.ship_state);



-- Should create an error
 insert into v_member (username) values ('bob');

-- Should create a record
insert into v_member
  (username, street, city, state, ship_street, ship_city, ship_state)
  values ('bob', '123 Main', 'Denver', 'CO', '543 Elm', 'Buttland',
'MS');

select * from member;
select * from addr;
select * from v_member;