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;
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... |