Help with inserts into Views

From: Brian Powell <brian(at)filogroup(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Help with inserts into Views
Date: 2000-06-08 19:54:28
Message-ID: B5655613.2F0E%brian@filogroup.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

Browse pgsql-sql by date

  From Date Subject
Next Message Stephen Crawley 2000-06-08 23:29:30 Is it possible to "truncate" a LOB?
Previous Message Ed 2000-06-08 19:39:58 Sum of datetime différence...