>
> 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?
>
If you did not mention you intend to insert/update a view via rules,
I'd say
create rule stash_id_in_music as on insert to inventorydo insert into music values(new.id, ...);
I personnally would prefer to do it via a trigger, because the trigger
function
allows for more flexibility on the action. But still, did I get you
right at all?
Regards, Christoph
