Received: from laura.owlscreech.com (IDENT:root@[208.177.146.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e58JshT97069 for ; Thu, 8 Jun 2000 15:54:43 -0400 (EDT) Received: from [208.177.146.11] (audrey.owlscreech.com [208.177.146.11]) by laura.owlscreech.com (8.9.3/8.8.7) with ESMTP id NAA18651 for ; Thu, 8 Jun 2000 13:54:28 -0600 User-Agent: Microsoft-Outlook-Express-Macintosh-Edition/5.02.2022 Date: Thu, 08 Jun 2000 13:54:28 -0600 Subject: Help with inserts into Views From: Brian Powell To: Message-ID: Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Archive-Number: 200006/98 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;