pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brian Powell <brian@owlscreech.com>
To: pgsql-sql@postgresql.org
Subject: creating rules on joined views
Date: Wed, 31 May 2000 12:55:09 -0600
Message-ID: <B55ABC2D.29D3%brian@owlscreech.com> (raw)

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




Message-ID: <B55ABC2D.29D3%brian@owlscreech.com>
Permalink:  ../B55ABC2D.29D3%25brian@owlscreech.com/
Also on:    postgresql.org/message-id/B55ABC2D.29D3%brian@owlscreech.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: brian@owlscreech.com
  Subject: Re: creating rules on joined views
  In-Reply-To: <B55ABC2D.29D3%brian@owlscreech.com>
  References: <B55ABC2D.29D3%brian@owlscreech.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox