creating rules on joined views

From: Brian Powell <brian(at)owlscreech(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: creating rules on joined views
Date: 2000-05-31 18:55:09
Message-ID: B55ABC2D.29D3%brian@owlscreech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

D'oh sorry for the subscribe message to the list...

Greetings, I am new to PostGreSQL and the advanced capabilities that it
offers. I would like to create a joined view that allows inserts and
updates on two different tables (but, joined twice).

Suppose you have:

create table user {
name varchar(40) not null,
email varchar(80),
address_id int4 not null,
shipping_address_id int4 not null
};

create table addresses {
address_id int4 primary key default nextval('address_seq_id'),
address varchar(80),
city varchar(80)
};

create view v_users as
select u.name
, u.email
, a1.address
, a1.city
, a2.address as ship_address
, a2.city as ship_city
from user u
, addresses a1
, addresses a2
where u.address_id = a1.address_id
and u.shipping_address_id = a2.address_id

Now, how would I create an insert rule on v_users because I have to insert
two addresses first, retrieve their id's and insert those into the user
table. Conversely, an update introduces its own challenges as well. If
someone could show me these pieces, I have pretty much figured out
everything else I need to do with the foreign keys, etc. It seems doable
easily with pgpsql; however, I cannot use that for a rule, correct?

Thanks,
Brian

Browse pgsql-sql by date

  From Date Subject
Next Message Jeff MacDonald 2000-05-31 19:00:55 Re: [HACKERS] Re: 7.0 weirdness
Previous Message mikeo 2000-05-31 18:41:17 TOP SESSIONS?