Rule definition problem

From: Jamie Lawrence <postgres(at)jal(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Rule definition problem
Date: 2003-04-24 19:11:09
Message-ID: 20030424191109.GY15321@jal.clueinc.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all -

I'm trying to define rules for inserts and updates on a view. My problem
is that I'm unsure on how to get the value of a field on one table and
use it in an insert into another.

The tables in question have a one-one relationship (A little warped, I
know; the 'inventory' table has data common to all items, and the
'music' table has data specific to, well, inventory items of type
'music'.)

The underlying tables and the view look something like this:

create table inventory (
id serial unique not null,
manufacturer_num text unique,
category_id int not null,
[...] );

create table music (
id serial unique not null,
inventory_id int not null,
year int,
label text,
[...]
CONSTRAINT category_exists foreign key (inventory_id)
references inventory (id) on delete cascade );

create view music_view as
select inventory.id,
inventory.manufacturer_num,
inventory.category_id,
[...],
music.year,
music.label,
[...]
from inventory, music
where inventory.id = music.inventory_id;

For an Insert rule, I need to get the value of inventory.id we just
inserted and stash it in music.inventory_id.

Is this possible in a rule, or do I need to do with in a trigger? Am I
on crack?

TIA.

-j

--
Jamie Lawrence jal(at)jal(dot)org
Non-linear physics is like non-elephant biology.

Browse pgsql-sql by date

  From Date Subject
Next Message Robert Treat 2003-04-24 19:22:24 Re: [SQL] rewriting values with before trigger
Previous Message Dennis Gearon 2003-04-24 19:10:39 Re: [SQL] rewriting values with before trigger